Checking SQL Service Running Status !!

Today I want to share one script that I use very frequently through Central Management Server to monitor the SQL services current running status.

This script will work from SQL Server 2008R2 SP1 onward as DMV dm_server_services was introduced from 2008R2 SP1.

Below is the script:

SELECT dss.[status_desc],servicename,startup_type_desc,service_account
FROM sys.dm_server_services dss
WHERE dss.[servicename] LIKE N’SQL%’


Hopefully this will be useful for you in monitoring your inventory.


Subhro Saha

SQL Server: Database Mail Error “Activation failure” !!


, ,

Recently in one of our legacy server running SQL Server 2005 SP2, I noticed that database mail was not working and was not sending any mails.  In database mail log, it was showing “Activation failure” error message. I recreated the mail profile and mail account but it did not help.

The solution is to replace the stored procedure named “[sp_sysmail_activate]” with below code in msdb database for SQL 2005:

— sp_sysmail_activate : Starts the DatabaseMail process if it isn’t already running

Alter PROCEDURE [dbo].[sp_sysmail_activate]

DECLARE @mailDbName sysname
DECLARE @mailEngineLifeMin INT
DECLARE @loggingLevel nvarchar(256)
DECLARE @loggingLevelInt int
DECLARE @parameter_value nvarchar(256)
DECLARE @localmessage nvarchar(max)
DECLARE @readFromConfigFile INT

EXEC sp_executesql @statement = N’RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue’

EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N’DatabaseMailExeMinimumLifeTime’,
@parameter_value = @parameter_value OUTPUT
IF(@rc <> 0)

–ConvertToInt will return the default if @parameter_value is null or config value can’t be converted
–Setting max exe lifetime is 1 week (604800 secs). Can’t see a reason for it to ever run longer that this
SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)

EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N’ReadFromConfigurationFile’,
@parameter_value = @parameter_value OUTPUT
–Try to read the optional read from configuration file:
SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0)

–Try and get the optional logging level for the DatabaseMail process
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N’LoggingLevel’,
@parameter_value = @loggingLevel OUTPUT

–Convert logging level into string value for passing into XP
SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
IF @loggingLevelInt = 1
SET @loggingLevel = ‘Normal’
ELSE IF @loggingLevelInt = 3
SET @loggingLevel = ‘Verbose’
ELSE — default
SET @loggingLevel = ‘Extended’

SET @mailDbName = DB_NAME()
SET @mailDbId = DB_ID()

EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
@mailEngineLifeMin, @loggingLevel
IF(@rc <> 0)
SET @localmessage = FORMATMESSAGE(14637)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
SET @localmessage = FORMATMESSAGE(14638)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage


After alter the stored procedure run the below code:

EXEC [sp_sysmail_activate]

It should be working now.

Hoping it will help you.


Subhro Saha

SQL Server: Stop Trace using SQL command !!


Recently, we were experimenting with one of the 3rd party tool in our environment that internally creates Traces. After sometime, there was some issue with the 3rd party tool and we stopped using it but it continued to produce the trace files on Server.

I used the below command to stop the trace:

— To Get the Trace ID

SELECT * FROM sys.traces

— Stop Trace


SET @TID = ? — Give the Trace ID retreived above

EXEC sp_trace_setstatus @TID,0
EXEC sp_trace_setstatus @TID ,2

Hope this will help you.


Subhro Saha

SQL Server tablediff Utility !!



Tablediff utility is one handy tool that I use in my day to day life as DBA to compare the data of one table against another table to identify if there are any differences between the tables. We can also generate the TSQL Script necessary to update the data in the second table to sync it with the other table.

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.

Now lets directly go into action and let me show you all how it works. For this I have created two tables with similar data in a Test database.

Below is the command: (type the command in command prompt in single line)

tablediff -sourceserver localhost -sourcedatabase Test -sourceschema Person -sourcetable ContactType -destinationserver localhost -destinationdatabase Test -destinationschema Person -destinationtable TestContactType

The command takes a few basic parameters to run

  • sourceserver
  • sourcedatabase
  • sourcetable
  • destinationserver
  • destinationdatabase
  • destinationtable


Here you can see that result says that both tables are identical. Now lets insert and update few data in both the tables so that we can sync them.

— Delete from Second Table
DELETE from [Person].[TestContactType]
where ContactTypeID=19

— Update single record in Second Table
Update [Person].[TestContactType]
set Name=‘Junior DBA’
where ContactTypeID=20

— Insert Record in the First Table
INSERT INTO [Person].[ContactType] (Name,ModifiedDate) values (‘Plumber’,GETDATE())

Now lets again run the command in tablediff to find out the data difference:


We can see it clearly highlights the 3 differences in these two tables. But the coolest feature of tablediff utility is the ability to generate TSQL scripts including the DML statements to update the destination table to sync it with the source table. To generate the script we need to add -f option.


Below script is generated :


Hope now you will be able to use this utility in a better way.


Subhro Saha

SQL Server: Removing Secondary Data File from Database !!


, ,

Today I want to show you how we can remove secondary data file from a database, now to do this, we need to first move the data from that file to other files in the same filegroup.

In below example, we will remove the Test_DB1 file from TestDB database.

Let us first get the details of all the files in our TestDB database:


In almost all cases, the file that we would want to remove will have some data that needs to be transferred to other files before we can remove this. To move the data we can use the below command:


Now to remove the Test_DB1 file from TestDB database we will run the below command:


There is no need to restart SQL server for this to take effect. We can verify that file has been dropped by again getting the details of files in TestDB database:


I hope you enjoyed this post.


Subhro Saha

SQL Server: SQL Agent Jobs getting Suspended State!!


, ,

Today we had to do some maintenance on one SQL Server and had to restore MSDB database. After restoring MSDB database we found that few of our SQL jobs were in Suspended state and we could not re-run them.

SQL Agent log showed messages like “Subsystem %s could not be loaded (reason: The specified module could not be found)”

This happens because of issues with the SQL Server Agent subs system information which  can be obtained from msdb.dbo.syssubsystems table.

SELECT * FROM msdb.dbo.syssubsystems


In our case the information was not correct and hence I DELETED and repopulated the table.

–- Delete the existing rows.

DELETE FROM msdb.dbo.syssubsystems

-– Re-fill the table with new rows pointing to the proper location of the DLLs.

EXEC msdb.dbo.sp_verify_subsystems 1

Problem should be solved after restarting SQL Server Agent.


Subhro Saha

SQL Server: Mirrored Database Backups !!


, ,

One of my DBA friend asked me if there is any other tool besides Robocopy to transfer database backups from Production Instance to another server for safekeeping. I asked him if he is aware that this feature is available since SQL Server 2005 Enterprise version and later versions but he was not aware that this feature already exists in SQL. So here I am writing about this useful feature that DBAs rarely use but it can be a very useful in situation where one of the Full, Differential or Tlog backup gets corrupted and we need to recover database from backups.

The only disadvantage of using this feature is that we need additional disk space to contain multiple backups.

A mirrored backup at a given time will allow us to create 2 – 4 identical copies of a database backup. Mirrored backup can be taken in local computer and also over the network.

Now let me give an example of Mirrored Full backup:

BACKUP DATABASE [AdventureWorks2012]
TO DISK = ‘S:\Database\Backup1\AdventureWorks_20140908_1.bak’
MIRROR TO DISK = ‘S:\Database\Backup2\AdventureWorks_20140908_2.bak’
MIRROR TO DISK = ‘S:\Database\Backup3\AdventureWorks_20140908_3.bak’
MIRROR TO DISK = ‘S:\Database\Backup4\AdventureWorks_20140908_4.bak’
,NAME = N’AdventureWorks2012-Full Database Backup’
,STATS = 10

We have to use the WITH FORMAT option to create new mirrored backup set else Database Backup Command with fail.

Hope this article explains the subject and if there is any issue then you can post a note and I will be happy to answer your question .


Subhro Saha

SQL Server 2005: Database Mail not working !!



Today we patched and upgraded one of our production SQL Server from SQL Server 2005 SP2 to SQL Server 2005 SP4. After upgrade our Database Mail that was configured to send mails from SQL Server stopped working and in the log below error was coming:

1) Exception Information
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Mail configuration information could not be read from the database.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.
DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.
CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.
Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.
MailOperation(Object o)

2) Exception Information
Exception Type: System.IndexOutOfRangeException
Message: timeout
Data: System.Collections.ListDictionaryInternal
TargetSite: Int32 GetOrdinal(System.String)
HelpLink: NULL
Source: System.Data

StackTrace Information
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.
DataAccessAdapter.GetAccount(Int32 accountID)

I deleted the Mail profile and Mail account and recreated them but still I kept on getting same error. I reinitialized Service Broker, checked the configuration in Surface Area and everything looked fine but our mail notification was not working…

After a lot of research, I found that we can rectify the issue by running the script named “sysdbupg.sql” from “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\install” ( location might be different if SQL installed in any other location)

This script basically upgrades all the SPs on System Databases.

Here are the sequence of steps I took before executing the script:

1) Took Full database backups
2) Ran the above script- it basically upgrades the SPs of System databases
3) Test SQL mail- and bingo!! It worked.
4) Again took System databases backups..

Hope this will help you someday!!

Subhro Saha

SQL Server: Find all Stored Procedures that run at SQL Startup !!


, ,

We can mark a Stored procedure to run when SQL server starts. For this to happen, we need to do the following:

  1. Create the Stored Procedure in the Master database
  2. We have to set the Startup flag for this particular Stored procedure.

For example, I want Stored Procedure named “usp_startup_log” to run whenever SQL Server starts. Here is how you do this:

exec sp_procoption N’usp_startup_log’, ‘startup’, ‘1’

Now if we want to list all the Stored procedure that are marked to run when SQL Server starts, then we can use below query :

SELECT name,create_date
FROM sys.procedures
WHERE is_auto_executed = 1

SQL Server: Storing Output of DBCC Statement in a Temp Table !!

In few servers, we have hundreds of databases and sometimes it is tiresome and time-consuming activity to find the output of a DBCC command such as DBCC SQLPERF(Logspace) for a particular database.

For this kind of scenario , we can store the output of DBCC commands in a temp table and then can quickly find the desired results using SORT or WHERE .

In this example, I am going to share the script that I use for storing the result DBCC SQLPERF command in a temp table:

IF OBJECT_ID(‘tempdb..#temp’, ‘U’) IS NOT NULL
DatabaseName VARCHAR(200)
,LogSize INT
,LogSpaceUsed INT
,[status] INT

EXEC (‘dbcc sqlperf(logspace);’)

FROM #temp

Hope this helps.


Subhro Saha