Alerting on Deadlocks with the SQL Server Management Pack

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:

 

 

 

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

 

 

 

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:

 

 

 

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

 

 

 

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

 

 

 

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

 

 

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

Comments

• Anonymous
  January 01, 2003
  quick but fantastic article!
  How did you find the two queries? :)
• Anonymous
  February 12, 2016
  I learned something new today. I had a client ask me about how to make SCOM monitor SQL logs. Generally
• Anonymous
  April 21, 2016
  Hmmm, based on my research, it looks like there are no less than 133 such alert-generating rules for which SQL does NOT log the corresponding event! :P I advised the MP Guide team to at least mention this fact in the next version of the guide, and to provide a script to enable this logging on the SQL side.