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:

 

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.


Comments (3)

  1. quick but fantastic article!
    How did you find the two queries? 🙂

  2. Anonymous says:

    I learned something new today. I had a client ask me about how to make SCOM monitor SQL logs. Generally

  3. TheSilverCanuck says:

    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! 😛 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.

Skip to main content