Alerting on Deadlocks with the SQL Server Management Pack

  • Article

Today a customer asked me how to configure SCOM to generate Alerts for SQL Deadlocks.  Looking in the SQL Server Management Pack, I found that we have event log Rules for deadlocks for SQL 2005, 2008, and 2012:

 

image

 

 

The Rules are targeted at DB Engine and alert on Event ID 1205 in the Application Event Log:

 

image

image

 

 

However, my customer generated a deadlock and no SCOM Alert was generated.  Looking in the Application Event Log on the SQL Server, we saw that the 1205 event was not logged.

 

After doing some digging, I found that that SQL Server does not log this event by default…which was confirmed by running Select * from sys.messages where message_id=1205 on the master database…the results showed is_event_logged=0:

 

image

 

 

To change this, we ran Exec sp_altermessage 1205, 'WITH_LOG', 'true' and verified the change (is_event_logged=1):

 

image

 

 

Now I generate a deadlock and get the 1205 event in the Application Event Log:

 

image

 

 

And I get an Alert from the SQL Server Management Pack:

 

image

 

To generate a deadlock, I used the steps documented here.