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 .

%3CLINGO-SUB%20id%3D%22lingo-sub-365777%22%20slang%3D%22en-US%22%3EAlerting%20on%20Deadlocks%20with%20the%20SQL%20Server%20Management%20Pack%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Oct%2028%2C%202014%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EToday%20a%20customer%20asked%20me%20how%20to%20configure%20SCOM%20to%20generate%20Alerts%20for%20SQL%20Deadlocks.%26nbsp%3B%20Looking%20in%20the%20SQL%20Server%20Management%20Pack%2C%20I%20found%20that%20we%20have%20event%20log%20Rules%20for%20deadlocks%20for%20SQL%202005%2C%202008%2C%20and%202012%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20833px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86954i11C65492262DB00B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Rules%20are%20targeted%20at%20DB%20Engine%20and%20alert%20on%20Event%20ID%201205%20in%20the%20Application%20Event%20Log%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20538px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86955iC546627C50E01D49%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20827px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86957i58489379145201D5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20my%20customer%20generated%20a%20deadlock%20and%20no%20SCOM%20Alert%20was%20generated.%26nbsp%3B%20Looking%20in%20the%20Application%20Event%20Log%20on%20the%20SQL%20Server%2C%20we%20saw%20that%20the%201205%20event%20was%20not%20logged.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20doing%20some%20digging%2C%20I%20found%20that%20that%20SQL%20Server%20does%20not%20log%20this%20event%20by%20default%E2%80%A6which%20was%20confirmed%20by%20running%20%3CEM%3E%20Select%20*%20from%20sys.messages%20where%20message_id%3D1205%20%3C%2FEM%3E%20on%20the%20master%20database%E2%80%A6the%20results%20showed%20is_event_logged%3D0%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20911px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86959i79994394AA37237A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20change%20this%2C%20we%20ran%20%3CEM%3E%20Exec%20sp_altermessage%201205%2C%20'WITH_LOG'%2C%20'true'%20%3C%2FEM%3E%20and%20verified%20the%20change%20(is_event_logged%3D1)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20745px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86960i046B21073EB6844B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20I%20generate%20a%20deadlock%20and%20get%20the%201205%20event%20in%20the%20Application%20Event%20Log%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20878px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86962iFD6CAAA9BAF8E480%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20I%20get%20an%20Alert%20from%20the%20SQL%20Server%20Management%20Pack%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20936px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86963i135A8207FDF8F4F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20generate%20a%20deadlock%2C%20I%20used%20the%20steps%20documented%20%3CA%20href%3D%22http%3A%2F%2Fstackoverflow.com%2Fquestions%2F22825147%2Fhow-to-simulate-deadlock-on-sql-server%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%20here%20%3C%2FA%3E%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-365777%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20TECHNET%20on%20Oct%2028%2C%202014%20Today%20a%20customer%20asked%20me%20how%20to%20configure%20SCOM%20to%20generate%20Alerts%20for%20SQL%20Deadlocks.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-365777%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EJimmyHarper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Feb 20 2020 11:26 AM
Updated by: