SQL Server : Caution while enabling Trace flag : (-T1204 or -t1204)

While working on a project ,I've come across situations where I had to add trace flags to my SQL Server instances for capturing useful information in SQL Server error log . Everyone know that how to do that but there might be chances that impact of -T (Capital T) and -t (small t) is not well calculated.

Trace flags can be used to alter the behavior of SQL Server and they can assist when diagnosing performance issues as well. Flags can be set for Session or Global (some are startup) levels though for later some can only be switched at startup using –T (you are suggested to avoid –t which turns on other internal flags) otherwise DBCC TraceOn/TraceOff (Flag # -1 though optional) comes quite handy.

-T > turns on only trace flag which is specified

-t > which turns on other internal flags along with the flag which is specified

How to check a trace flag is enabled or disabled?

Using method DBCC TRACESTATUS, we can check the status of a trace flag.

Ex: DBCC TRACESTATUS (-1) will display list of trace flags enabled at global level

DBCC TRACESTATUS () will display list of trace flags enabled for current session

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Creating an instance of the COM component with CLSID: Problem with creating maintenance plans

While working on a project to configure backup job , I had problem with creating maintenance plans in SQL 2008 R2, x64 as when tried to open maintenance plans got below mentioned error message . Second, it might happen that one can open the maintenance plans wizard, but at the time of submission, same error appears so same can be solved with below mentioned workaround.

 

Error Message:

 

=====================================================================================================================================

 

 Creating an instance of the COM component with CLSID {17BCA6E8-A95D-497E-B2F9-AF6AA475916F} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

 

=====================================================================================================================================
Program Location:

 

at Microsoft.SqlServer.Dts.Runtime.Package..ctor()

 

OS: Windows 2008 R2 server SP1 (64bit)
SQL Server: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

 

Workaround/ Fix :

 

1) Traverse to the C:\Program Files\Microsoft SQL Server\100\DTS\binn directory and run the following from the command:

 

REGSVR32.EXE dts.dll

2) You have to close the SSMS and re-open which will solve the problem.

 

 

 

Brgds, 

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : EMC Legato NetWorker :Stop Archiving and Resolve high CPU utilization

As it is primary responsibility of production DBA to validate if daily backup jobs are running successfully if backup is happening via Database Engine or query msdb database to check if backup ran fine if configured via any third Party tool.

In my case, Backup is happening via Database engine but due to space crunch file is being archived to tape on daily basis in order to ensure that we have valid backup in safe location if next backup job failed and we have deleted the older one.

All was working as expected for long time but with time progress, we frequently come  across the situation where archiving started taking long time.

This time, weekend job failed to backup due to space crunch on underlying mount point and space crunch occurred cause archiving process seems in hung state.

In order to resolve above said issue, in EMC Legato Networker, follow the below mentioned steps to stop the archiving process and delete the older backup file to ensure , there is sufficient space for next backup.

Steps:

1. GO to Task Manager

2. Sort processes by name

3. Point all save.exe and kill each process

4. Delete the backup file

In my case , i found 2 processes named save.exe so after killing both , i was able to delete the older backup file.

Now, Next backup can be performed.

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : could not connect to the Distributor using the specified password - Transactional Replication

While working on a project to configure Transactional replication on Production database as a reporting solution, where requirement was to setup remote distributor (in my case: On subscriber server), I was able to successfully configure the distributor but when I was trying to configure the publication using remote distributor, I faced below mentioned error and that was causing me to move forward.

As I found the solution so it forces me to share with all if this can save you in critical time if you are in trouble due to this error.

Error: SQL Server could not connect to the Distributor using the specified password.

By seeing error message , it seems that it is simple error and perhaps I mentioned wrong password but I was sure that this is not the case.

There are couple of things which should be validated in order to resolve this issue and last option C worked for me.

A) I have tried changing password for distributor account by following below mentioned steps but this also didn't help me as expected.

1- Connect to the server where your distribution db resides
2- Right click on the replication folder and select distributor
properties.
3- Select publishers. If you have already entered your publisher
uncheck and select OK
4- Add a new publisher
5- Enter a password for the administrative link
6- Connect to your publishing server and and create a new publication
using the remote distributor.
7- Enter the password you used earlier when assigning the publisher in
the distribution properties

B) Check if the remote access parameter in sp_configure is enabled on the Publisher. In my case it was already enabled so no luck this time as well. Finally I tried the option C and that worked for me like a magic.

C) Check if SQL Server is running in fiber mode (Check if ‘Lightweight Pooling’  parameter in sp_configure is enabled on all involved instances) . In my case it was so I turned off ‘Lightweight Pooling’ parameter and issue was resolved.

P.S: SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server - Configure Logshipping on database where Mirroring is already configured

While working on a project to configure Log-shipping on couple of Production databases as a Disaster Recovery solution where databases were already part of Mirroring as a High Availability solution, Initially I configure the Log-shipping from Principal database and all was working until database failover happen from Principal to Mirror.

Now, as there was no configuration from Mirrored copy of database so DR site was out of sync from so many days and it was the reason which forces me to configure Logshipping from Mirror copy of databases as well.

I completed this configuration on our production environment and same is working fine as expected so I thought to share this with all who might be interested in doing same.

Steps:

1. Backup share (where all T-Log backup will be placed for Log Shipping) should be a shared location as in my case , I used Witness server and created folder named LogShip as a shared folder and granted appropriate rights to SQL Agent service account

2. Configure Logshipping from Principal copy of database with standard process via T-SQL/ GUI as per your preference

3. Here is the trick: Script out logshipping which you configured from Principal database and run only backup script on Mirror server.

4. Job will be enabled on both but Logshipping will not raise alert if backup is not happening from mirror copy.

Hope this will help ..!!

Brgds,

Chhavinath Mishra  

Database Administrator

Microsoft Certified IT Professional (MCITP
)

SQL Server - Installation - security prerequisites

SQL Server - Installation - security prerequisites

In order to run correctly, all SQL Server services accounts must respect some security prerequisites, according with Microsoft recommendations – in brackets, security privilege. Please add them manually or by scripts. These permissions are applied locally on the server for SQL Server domain or local services accounts:

Act as part of the operating system (SeTcbPrivilege)

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

Bypass traverse checking (SeChangeNotifyPrivilege)

Create a token object (SeCreateTokenPrivilege)

Impersonate a client after authentication (SeImpersonatePrivilege)

Log on as a batch job (SeBatchLogonRight)

Log on as a service (SeServiceLogonRight)

Replace a process level token (SeAssignPrimaryTokenPrivilege)

Perform volume maintenance tasks(SeManageVolumeNamePrivilege)

Lock pages in memory (SeLockMemory)

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server --Query to Identifying Missing Indexes

SELECT
statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

-- avg_user_impact:- This column represents the value in percentages. It informs us the amount of improvement which you can get if the index is created.

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server - Script to get Tables name, size and rows

SQL Server - Script to get Tables name, size and rows

Applicable on 2005/2008 & R2:

USE [DatabaseName]
GO

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)