Pages

Sunday 15 July 2012

SQL Server: Instant Deadlock Alert Using WMI in Your Mailbox

In my last post I have explained,how to setup alert for blocking using WMI. In this post let us see how to set up an alert for dead lock, which will help us to trouble shoot the dead lock scenarios.

As I explained in the last post, we need to do a configuration change in 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. Restart the SQL server agent service.Read more about the this on MSDN.


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 two fields .A date column to store date and time of deadlock and a Xml column to store the deadlock graph. The script to create this table is available here (The first part). Create Procedure DBA_Deadlock_graph using the script available in the second part of the above mentioned script.



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_DeadLock_Graph. In the step add the last part of the script mentioned above.

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 instancename with your instance name. 

In the query section add 
SELECT * FROM  DEADLOCK_GRAPH 






























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 deadlock and wait 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 deadlock is not occurred 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 DeadLockEvents. 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 DeadLockEvents 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 procedure DBA_Deadlock_Graph. 
Hope this will help you to implement custom deadlock alert in your environment. 


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

16 comments:

  1. How is the DEADLOCK_GRAPH table created? There is no script for it.

    ReplyDelete
    Replies
    1. There is no table called deadlock_graph, the query that we use select * from deadlock_graph in the alert is to capture WMI event

      Delete
    2. Thanks for your reply Nelson! But there is no table by that name. So is it created whenevr the job is invoked the first time?
      And what should be the appropriate value/token for "TextData" in $(ESCAPE_SQUOTE(WMI(TextData)))

      Delete
  2. THanks for the post Nelson. I have locking events working, but I'm having a little trouble with the deadlock events. I followed your directions and I'm getting this error: Unable to start execution of step 1 (reason: Variable WMI(TextData) not found). The step failed.

    Any ideas what's wrong?

    ReplyDelete
    Replies
    1. The only reason I can see is the sql server agent replace token properties, but that shouldn't be a problem if the blocking alert is working.Could you please check the the code again..might be broken the line somewhere while copying ..something like that..
      The code which I posted is copied from our environment

      Thanks
      nelson

      Delete
  3. Ensure that Token Replacement option is checked in the Agent properties :
    SQL Server Agent >> Alert System >> Token Replacement >> Check the Replace tokens for all job responses to alerts option.

    ReplyDelete
  4. Nelson..
    what do I need to use to create table MSDB or my application DB name?

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This is really useful for monitoring deadlocks, many many thanks!

    Matt

    ReplyDelete
  7. Nice Blog.Thank you for Sharing. We are leading erp software software solution providers in chennai. For more details call +91 9677025199.
    erp software in chennai | erp providers in chennai | online events registration

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

    ReplyDelete
  9. Great knowledge, do anyone mind merely reference back to it digital resource of ebooks

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

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

    ReplyDelete