First published on TECHNET on Oct 28, 2014
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 .