Tuesday, July 16, 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.

    1. 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;
    1. Assure that SQLMail is configured
      1. NOTE: The step I forget most often is to assure that mail is enabled under SQL Server Agent -> Properties, as is outlined here.
    2. Create an alert for 1205 message.
  1. NOTE: This will only set up the notification.  You also have to configure a default monitor to detect deadlocks.  The mail will only prompt you to go and have a look at the deadlock trace!

References used in compiling this procedure:

Info on the error message I encountered in using sqlmail with alerts:
Error: An attempt was made to send an email when no email session has been established
Link  ; http://www.sqldbadiaries.com/2010/07/27/an-attempt-was-made-to-send-an-email-when-no-email-session-has-been-established-sql-server-2005/

Procedure for setting up alerts:

Test Script to simulate deadlock:

Scripting out existing database mail configuration

SQL Server Central Artical on Scripting out Existing database mail configuration I wanted to save this here so I can easily find it. Sorry ...