SQL Server: Instant Blocking Alert Using WMI in Your Mailbox

As a DBA, it is important for all of us to get real time alert on various issues in our inbox to be more proactive and to manage the database servers in better way.There are many third party tools which you can easily configure to monitor the servers and to get the alerts. I feel more happy when we implement the required alert by our self to get the alert. we have implemented many custom alerts in our environment. I will share the important ones in this blog.

There are many method that you can adopt to implement custom alert. I have done this using WMI (Windows Management Instrumentation) .WMI is very powerful one to monitor the performance of the servers and application.

Blocking will happen when one process need  to wait for a resource(example lock) which currently using by another process. The first process has to wait till  the second process completes its action and release the resources.

Let us see how we can set up the alert for blocking. The first step to create blocking alert is configure the blocked process threshold values.This is server wide configuration which can be set using sp_configure.The value of blocked process threshold specify the threshold, in seconds,at which blocked process reports are generated.By default no blocked process reports are produces.In simple words, if the value configured as 30 second, we will get alert when a process is blocked for more than 30 seconds. This can be done using the below code.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'blocked process threshold (s)',30
GO
RECONFIGURE WITH OVERRIDE
Another configuration change that we need to do in the SQL server agent. Right click on the agent node , select properties. On the left pane select Alert System. On bottom of that page tick the check box of Replace tokens for all job responses to alert. Read more about the this on MSDN.Restart the SQL server agent service.

The next step is create a simple table in one database.In all our environment we have a database to implement the administrative tasks. This table contains only four fields .One identity column, a date column to store date and time of blocking,a Xml column to store the blocked processed report and a integer column to store the blocking process spid. The script to create this table is available here

USE [DBAMonitor] GO --Create this table to store the history of the blocked events CREATE TABLE [dbo].[BlockedEvents]( [Event_id] [int] IDENTITY(1,1) NOT NULL, [AlertTime] [datetime] NULL, [BlockedReport] [xml] NULL, [SPID] [int] NULL, PRIMARY KEY CLUSTERED  ( [Event_id] ASC ) GO /* Use below script in the step of job DBA_BlockedAlert */ SET QUOTED_IDENTIFIER ON DECLARE @blockingxml XML SELECT  @blockingxml = N'$(ESCAPE_SQUOTE(WMI(TextData)))' CREATE TABLE #BlockingDetails ( Nature VARCHAR(100), waittime VARCHAR(100), transactionname VARCHAR(100), lockMode VARCHAR(100), status VARCHAR(100), clientapp VARCHAR(100), hostname VARCHAR(100), loginname VARCHAR(100), currentdb VARCHAR(100), inputbuf VARCHAR(1000) ) --Blocked process details INSERT INTO #BlockingDetails SELECT  Nature = 'Blocked', waittime = isnull(d.c.value('@waittime','varchar(100)'),''), transactionname = isnull(d.c.value('@transactionname','varchar(100)'),''), lockMode = isnull(d.c.value('@lockMode','varchar(100)'),''), status = isnull(d.c.value('@status','varchar(100)'),''), clientapp = isnull(d.c.value('@clientapp','varchar(100)'),''), hostname = isnull(d.c.value('@hostname','varchar(100)'),''), loginname = isnull(d.c.value('@loginname','varchar(100)'),''), currentdb = isnull(db_name(d.c.value('@currentdb','varchar(100)')),''), inputbuf = isnull(d.c.value('inputbuf[1]','varchar(1000)'),'') FROM @blockingxml.nodes('TextData/blocked-process-report/blocked-process/process') d(c) --Blocking process details INSERT INTO #BlockingDetails SELECT  Nature = 'BlockedBy', waittime = '', transactionname = '', lockMode = '', status = isnull(d.c.value('@status','varchar(100)'),''), clientapp = isnull(d.c.value('@clientapp','varchar(100)'),''), hostname = isnull(d.c.value('@hostname','varchar(100)'),''), loginname = isnull(d.c.value('@loginname','varchar(100)'),''), currentdb = isnull(db_name(d.c.value('@currentdb','varchar(100)')),''), inputbuf = isnull(d.c.value('inputbuf[1]','varchar(1000)'),'') FROM @blockingxml.nodes('TextData/blocked-process-report/blocking-process/process') d(c) DECLARE @body VARCHAR(max) SELECT @body = ( SELECT td =  currentdb + '</td><td>'  +  Nature + '</td><td>' + waittime + '</td><td>' + transactionname + '</td><td>' +  lockMode + '</td><td>' + status + '</td><td>' + clientapp +  '</td><td>' +  hostname + '</td><td>' + loginname + '</td><td>' +  inputbuf FROM #BlockingDetails FOR XML PATH( 'tr' )      )   SELECT @body = '<table cellpadding="2" cellspacing="2" border="1">'                   + '<tr><th>currentdb</th><th>Nature</th><th>waittime</th><th>transactionname</th></th></th><th>lockMode</th></th>               </th><th>status</th></th></th><th>clientapp</th></th></th><th>hostname</th></th>               </th><th>loginname</th><th>inputbuf</th></tr>'                   + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )                    + '</table>'  +  '<table cellpadding="2" cellspacing="2" border="1"><tr><th>XMLData</th></tr><tr><td>' + replace( replace( convert(varchar(max),@blockingxml),  '<','&lt;' ),  '>','&gt;' )                 + '</td></tr></table>' DROP TABLE #BlockingDetails --Sending Mail DECLARE @recipientsList varchar(8000) SELECT @recipientsList ='xx@yahoo.com;yy@yahoo.com' EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'MyMailProfile', --Repalce this with your profile name     @recipients = @recipientsList,     @body = @body,     @body_format = 'HTML',     @subject = 'Alert! Blocking On ABC Live Server',     @importance = 'High' ; --Inserting into a table for further reference INSERT INTO DBAMonitor.dbo.BlockedEvents                 (AlertTime, BlockedReport)                 VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData)))') --Updating the SPID column UPDATE B SET B.SPID = B.BlockedReport.value('(/TextData/blocked-process-report/blocking-process/process/@spid)[1]','int') FROM DBAMonitor.dbo.BlockedEvents B  where  B.Event_id = SCOPE_IDENTITY()

The next step is to create a new job.The specialty of this job is, it does not have a schedule.Let us create a job namely DBA_BlockedAlert. In the step add the second part of the script mentioned in here

The final step is to create an alert . Refer below screenshot to create the alert. Mention an appropriate name for the alert. Select alert type as WMI event alert.In the name space add

\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME .
Replace the instance name with your instance name.
In the query section add SELECT * FROM BLOCKED_PROCESS_REPORT

In the response page tick the Execute job check box and select the job that we have created earlier.

Now everything is set. Create a blocking and wait for 30+ seconds to get the alert in your mail box. In case if your are not receiving the alert follow below point to troubleshoot.

• Check alert history and see number of occurrences. If it is still zero either blocking is not passed 30 seconds or there is some problem with WMI. Check your WMI service status. if it is running , restart the WMI service. 
• If the number of occurrence is greater than zero, check the table BlockedEvents. If there is no entries, the token replacement may not be happening. Check the   Replace tokens for all job responses to alert of SQL server agent properties. Make sure that you have restarted the SQL Server agent service after making this change.
• If there is an entry in the BlockedEvents table, there is something wrong with your database mail configuration /mailbox. Check the database mail is working and you have mentioned the correct profile name in the job step.

Hope this will help you to implement custom blocking alert in your environment.