Pages

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

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.


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

17 comments:

  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?


    DKShah

    ReplyDelete
  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,

    Kasi

    ReplyDelete
    Replies
    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

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

      Kasi

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  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);

    SELECT @instance = CONVERT(NVARCHAR, ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER'))
    SET @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instance


    EXEC msdb.dbo.sp_add_alert @name=N'Blocked Process',
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=600,
    @include_event_description_in=1,
    @category_name=N'[Uncategorized]',
    @wmi_namespace=@namespace,
    @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT',
    @job_name=N'Utility - Blocked Process Alert'
    GO

    ReplyDelete
  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 ?

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

    ReplyDelete
  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?

    ReplyDelete
  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!

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

    ReplyDelete
  10. Interesting post! This is really helpful for me. I like it! Thanks for sharing!
    seo lüdenscheid

    ReplyDelete
  11. Dropbox links are not working anymore

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

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

    ReplyDelete
  14. Since countries have different systems of cultural, legal and business administration, without local knowledge and experience, setting up a company and registering it in another country can be a problem. http://www.confiduss.com/en/jurisdictions/japan/demographics/

    ReplyDelete