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. Great tasks...........
    Thanks Lot..

    ReplyDelete
  2. 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
  3. 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
  4. 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
  5. Nelson..
    what do I need to use to create table MSDB or my application DB name?

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

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

    Matt

    ReplyDelete



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

    ReplyDelete
  9. 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
  10. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

    ReplyDelete