top of page

Enable deadlock alerting

  • Adam Thurgar
  • Nov 4, 2019
  • 1 min read

One of the difficulties in dealing with deadlocks is actually knowing that they occurred and then getting information. Even if you have enable the right trace flags, you are only part of the way there. The 1205 deadlock message is not logged and this means that it could be missed. So you need to log this message by

EXEC master..sp_altermessage 1205, 'WITH_LOG', true;

Then you can add an alert for message_id 1205.

With this alert I then call the SQL Server Agent job for sp_whoisactive (to a logging table) to catch what is happening at the time the 1205 alert happened. Then use the the system_health extended events for the xml_deadlock_report.


 
 
 

Recent Posts

See All
Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a...

 
 
 
Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response...

 
 
 
Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete...

 
 
 

Comments

Couldn’t Load Comments
It looks like there was a technical problem. Try reconnecting or refreshing the page.
bottom of page