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.