Alerting on Deadlocks with the SQL Server Management Pack

Published 03-12-2019 05:13 PM 1,091 Views
Senior Member

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 .

Version history
Last update:
‎Feb 20 2020 11:26 AM
Updated by: