Showing posts from July 14, 2013

Getting immediate notifications for deadlocks in SQL Server

Today I learned that I can get immediate notification of deadlocks in SQL Server.  This is very useful, since one of our applications is prone to deadlocks (due to it's remarkable configurability), and these can be difficult to reproduce, especially in production.

First - a disclaimer.  This method does not email the deadlock graph to you, only an alert saying that the deadlock has occurred.  It is assumed that you are running a server-side trace to capture the necessary information for trouble-shooting.  This is only to give you the alert.

Assure that the 1205 message is actually logged. (very good info on this in Michael K. Campbell's article for Practical SQL Server)                      EXEC master..sp_altermessage 1205,'WITH_LOG',TRUE;                      GOAssure that SQLMail is configuredNOTE: The step I forget most often is to assure that mail is enabled under SQL Server Agent -> Properties, as is outlined here.Create an alert for 1205 message.Test with scri…