SQL Server Deadlock Notifications

An Event Notification is a database object that can be used to capture and relay information about server and database events to the DBAs, through the Service Broker service. 

 

DBAs can use the Event Notifications to monitor many types of events, such as deadlocks and blocking.  In this tip, I am going to show you how to monitor your deadlocks.  There are several objects you'll need to create first, and then we create a deadlock to test the notification.

/* Create Event Notification Queue */
  CREATE QUEUE DeadLockNotificationQueue
  WITH STATUS = ON,
  ACTIVATION (
    PROCEDURE_NAME = usp_DeadlockEvents,
    MAX_QUEUE_READERS = 1,
    EXECUTE AS 'dbo' );
  GO

 /* Create Event Notification Services */
  CREATE SERVICE DeadlockNotificationSvc
  ON QUEUE DeadLockNotificationQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
  GO

 /* Create route for the DeadlockNotificationSvc */
  CREATE ROUTE DeadLockNotificationRoute
  WITH SERVICE_NAME = 'DeadLockNotificationSvc',
  ADDRESS = 'LOCAL';
  GO

 /* Create Event Notification for the deadlock_graph event. */
  CREATE EVENT NOTIFICATION DeadLockNotificationEvent
  ON SERVER
  FOR DEADLOCK_GRAPH
  TO SERVICE
    'DeadLockNotificationSvc',
    'current database'
  GO

 /* Create DeadlockEvents table (to hold all of our deadlock details). */
  CREATE TABLE dbo.DeadlockEvents (
   DeadlockID INT IDENTITY(1,1),
   EventMsg XML,
   EventDate DATETIME NOT NULL CONSTRAINT df_DeadlockEvents_EventDate DEFAULT (GETDATE())
  ) ON [DatabaseName_Data]
  GO

 /* Procedure writes the deadlock event to DeadlockEvents, and emails our DBA Team. */
 CREATE PROCEDURE dbo.usp_DeadlockEvents
 AS
 SET NOCOUNT ON;
/* Captures our deadlocks, writes them into DeadlockEvents table, and emails DBA Team. */
  DECLARE @msgBody XML   
  DECLARE @dlgId uniqueidentifier

  WHILE(1=1)
  BEGIN
  BEGIN TRANSACTION   
  BEGIN TRY       

    /* Process messages from queue one at a time. */
    ;RECEIVE TOP(1) @msgBody = message_body, @dlgId = conversation_handle
    FROM dbo.DeadLockNotificationQueue

     /* Exit when whole queue is processed. */
     IF @@ROWCOUNT = 0
     BEGIN
       IF @@TRANCOUNT > 0
       BEGIN
           ROLLBACK;
       END 
       BREAK;
     END

     /* Write event data into our DeadlockEvents entity. */
     INSERT dbo.DeadlockEvents (eventMsg)
     SELECT @msgBody

     DECLARE @MailBody NVARCHAR(MAX)   
     SELECT @MailBody = CAST(@msgBody AS NVARCHAR(MAX));

    /* Send email to your DBA Team. */
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'MailProfileName',
      @recipients = 'DBATeam@YourCompany.com',
      @subject = 'A Deadlock has occurred!!',
      @body = @MailBody,
      @importance = 'High';

      IF @@TRANCOUNT > 0
      BEGIN
         COMMIT;
      END

  END TRY
  BEGIN CATCH

     IF @@TRANCOUNT > 0
     BEGIN
         ROLLBACK;
     END

      /* Write error(s) into the Event log. */
      DECLARE @errorNumber BIGINT, @errorMessage nvarchar(2048), @dbName nvarchar(128)
      SELECT @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @dbName = DB_NAME()
      RAISERROR (N'Error receiving Service Broker message from  DeadLockNotificationsQueue.
      DATABASE Name: %s; Error number: %I64d; Error Message: %s',
      16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG;

  END CATCH;
END

SET NOCOUNT OFF;
GO

/*
TEST YOUR DEADLOCK EVENT CAPTURE
First create your TestDeadlocks table.
Open two windows in SSMS, copy Query #1 into one window, and Query #2 into the other.
Execute the queries in parallel to produce your deadlock event.
*/
  USE YourDatabase;
  IF OBJECT_ID('TestDeadlocks','U') > 0
  DROP TABLE dbo.TestDeadlocks
  GO 
  CREATE TABLE dbo.TestDeadlocks (ID INT)
  INSERT dbo.TestDeadlocks (ID)
    SELECT 1
  UNION ALL
    SELECT 2
  GO

   /* Query #1 */
   BEGIN TRAN
      UPDATE dbo.TestDeadlocks
      SET ID = 12
      WHERE id = 2

      -- Wait 5 seconds (this creates the deadlock condition in 2nd window)
      WAITFOR DELAY '00:00:05'

      UPDATE dbo.TestDeadlocks
      SET ID = 11
      WHERE id = 1
   COMMIT

   /* Query #2 */
   BEGIN TRAN
      UPDATE dbo.TestDeadlocks
      SET ID = 11
      WHERE ID = 1
    
      -- Wait 5 seconds (this creates the deadlock condition in 1st window)
      WAITFOR DELAY '00:00:05'

      UPDATE dbo.TestDeadlocks
      SET ID = 12
      WHERE ID = 2
   COMMIT
 
  /* Run this after your deadlock has occurred. */
   SELECT * FROM dbo.TestDeadlocks
   ORDER BY ID



Take a look at these to review more information on Event Notifications:

Event Notifications:                 http://technet.microsoft.com/en-us/library/ms182602(v=sql.105).aspx
Understanding Event Notifications:   http://technet.microsoft.com/en-us/library/ms190427(v=sql.105).aspx
Designing Event Notifications:       http://technet.microsoft.com/en-us/library/ms175854(v=sql.105).aspx
Implementing Event Notifications:    http://technet.microsoft.com/en-us/library/ms178080(v=sql.105).aspx