Thursday, 12 July 2012

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


EXEC sp_configure 'blocked process threshold (s)',30

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

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 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.

If you liked this post, do like my page on FaceBook 


  1. Can you please put script to create table & Job step. When I clicked on here link to get script it retruns nothing. Can you please provide script for that?


  2. Hi John,

    This is really Nice, I tried for 30 mins (EXEC sp_configure 'blocked process threshold (s)',1800), but is not working for that 30 mins threshold. Is there any limit for the threshold, Please let me know any other way if I want to get the blocking alert if it is blocking for 30 mins.

    Thanks in advance,


    1. As far as I know, there is no limit for threshold value, but you can test first with 30 or 60 seconds.Some time it might be problem with WMI or alert settings.Try to troubleshoot with the points given at the end of the points

    2. John,

      Thanks for you reply. I have already tested it for 30 and 60 seconds, it is working greatly. And also I have observed that if go for a threshold of 15 minutes (EXEC sp_configure 'blocked process threshold (s)',900) then I am able to get alert after 30 minutes which is abnormal.


  3. This comment has been removed by the author.

  4. John,

    I love what you have done. I've implemented it several times. How about scripting the alert to get the current instance name -something like this:

    DECLARE @instance NVARCHAR(64), @namespace NVARCHAR(512);

    SET @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instance

    EXEC msdb.dbo.sp_add_alert @name=N'Blocked Process',
    @job_name=N'Utility - Blocked Process Alert'

  5. Very useful...I tried to put the entire code in the job as an stored proc with parameters like @EmailAlert (1- sends email, 0 doesn't), @Receipients, @ProfileName, but it doesn't seem to work, but if the entire text is put in the job it work...would like to know why ?

  6. The understanding of the ins and outs connected with system buildings requirements in which pros linked to SQL server jobs opportunities, will be able to comply with the reason from the movement connected with info throughout the system, because the SQL Server Careers will most likely require solving in addition to steering clear of bottlenecks that will damage accomplishing this.

  7. For some reason the alert keeps sending emails long after the the block has been cleared. The only way we can stop it is to disable the alert. Ideas?

  8. I tried it on SQL Server 2012. The alert works but I get an empty email, empty table and empty trace file. I set the threshold at 30 seconds. Please help!


  9. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune


  10. I loved the way you discuss the topic great work thanks for the share, Let me share this, dotnet training in pune

  11. This does not seem to work on a database that has READ_COMMITTED_SNAPSHOT enabled. the alert registering for blocking events in all other databases OK, but nothing for RCS. If anyone has any insights into catching it, please share.

  12. Thank you for this valuable information. The information you shared is very interesting. Get your business to the next level in simple steps. We provides lowest price of erp software Software for our clients.
    erp software in chennai | erp providers in chennai | online events registration

  13. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث