Or….deadlock notifications.

In my previous post I talked about having your own database(s) and what information you could be collecting. One piece of information that you should be collecting is what deadlocks are occurring. There are a few of ways you can collect information on deadlocks, enabling trace flag 1222 and querying the error log, running profiler (urgh!), or querying the system_health event session (in SQL Server 2008 onwards).

In this post, I’d like to talk through setting up Event Notifications and Service Broker in order to be alerted when a deadlock occurs. I like this way of alerting for deadlocks as I think that you can get the most amount of information with minimal (if any) performance impact. I also administer instances of 2005 to 2012 and like to have one solution that fits all.

DISCLAIMER!! Please don’t do this for the first time in a live environment. Create a test database on a dev server so that you can play around (see the demo at the end).

Pre-requisite – DBMail setup and configured.

Right, here we go! Create a test db and then enable Service Broker for your database and set the TRUSTWORTHY property to ON. The TRUSTWORTHY property needs to be set to ON so that the stored procedure we are going to create later can run sp_send_dbmail.

USE [master];
GO
CREATE DATABASE [DeadlockDemo];
GO
ALTER DATABASE [DeadlockDemo] SET ENABLE_BROKER;
GO
ALTER DATABASE [DeadlockDemo] SET TRUSTWORTHY ON;
GO

Second thing to do is create a table to hold the information on the deadlocks

USE [DeadlockDemo];
GO
CREATE TABLE [dbo].[DeadLockLog] 
([DeadLockID]       INT          IDENTITY (1, 1) NOT NULL,
 [DeadLockDetected] DATETIME     NULL,
 [DatabaseName]     SYSNAME      NULL,
 [DeadLockXML]      XML          NULL,
PRIMARY KEY CLUSTERED ([DeadLockID] ASC));
GO

We then need to create a queue to hold messages and a service to receive messages from the Event Notification we will setup. We specify [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] as the message type and contract (basically the direction of messages, from Event Notification to Service).

CREATE QUEUE [DeadlockQueue];
GO
CREATE SERVICE [DeadlockService]
ON QUEUE [DeadlockQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

Then run the following statement to create the Event Notification:-

CREATE EVENT NOTIFICATION [CaptureDeadlocks]
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE N'DeadlockService', N'current database';
GO

It’s pretty straight forward, server wide, for deadlocks, to the service we have setup within the database. OK, next bit. When a new message about a deadlock is received, we want to record information on that deadlock and then be emailed. The way to do this is to create a stored procedure (substitute your own DBMail profile and email address in):-

CREATE PROCEDURE [dbo].[DeadlockNotification]
AS
DECLARE @DeadlockInfo      XML
DECLARE @EventTime         DATETIME 
DECLARE @MsgBody           XML
DECLARE @MsgType           SYSNAME
DECLARE @DBName            SYSNAME
DECLARE @DBMailBody        SYSNAME
DECLARE @DBMailSubject     SYSNAME
DECLARE @DBMailRecipient   SYSNAME
DECLARE @DBMailProfile     SYSNAME
SET @DBMailProfile   = 'YOUR DBMAIL PROFILE'
SET @DBMailRecipient = 'YOUR EMAIL ADDRESS'
--Retrieve message from queue
WAITFOR 
(RECEIVE TOP(1) 
@MsgBody= CAST([message_body] AS XML),
@MsgType = message_type_name
FROM [DeadlockQueue]), TIMEOUT 6000;
--Retrieve information from the XML
SELECT 
@DeadlockInfo = @MsgBody.query('(/EVENT_INSTANCE/TextData/deadlock-list)'),
@DBName       = DB_NAME(@MsgBody.value('(//*/process/@currentdb)[1]', 'varchar(50)')),
@EventTime    = @MsgBody.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
--Insert record into table if message conforms to correct type
IF @MsgType = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
INSERT INTO [dbo].[DeadLockLog]
(DeadLockDetected, DatabaseName, DeadLockXML) 
VALUES 
(@EventTime, @DBName, @DeadlockInfo)
--Set values to be included in email
SET @DBMailSubject = 'A deadlock has occurred on ' + @@SERVERNAME
SET @DBMailBody    = 'A deadlock occurred in database ' + 
@DBName + ' at ' + CONVERT(VARCHAR(20),@EventTime)
--Send email if message conforms to correct type
IF @MsgType = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
EXEC [msdb].[dbo].sp_send_dbmail
@profile_name= @DBMailProfile,
@Recipients= @DBMailRecipient,
@subject= @DBMailSubject,
@body= @DBMailBody;
GO

The procedure retrieves the message from the queue, pulls (or “shreds”) information from the XML, inserts it into a table and sends an email. Cool eh?

Finally, execute the following statement so that the stored procedure will run when a new message arrives in the queue:-

ALTER QUEUE [dbo].[DeadlockQueue]
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = [dbo].[DeadlockNotification],
      MAX_QUEUE_READERS = 1,
      EXECUTE AS SELF
    );
GO

TESTING!!!!!

You can then test the functionality (in the dev environment that you have been doing this in……right?) by creating two tables and running concurrent UPDATE and SELECT statement in open transactions. Here’s the setup:-

CREATE TABLE [TEST1]
(ID1 INT);
GO
CREATE TABLE [TEST2]
(ID2 INT);
GO
INSERT INTO [TEST1]
(ID1)
VALUES
(100)
GO 1000
INSERT INTO [TEST2] 
(ID2)
VALUES
(100)
GO 1000

All setup? Now run the following:-

USE [DeadlockDemo];
GO
BEGIN TRAN
UPDATE [Test1] SET ID1 = 1 WHERE ID1 = 100

Open up another session, and run:-

USE [DeadlockDemo];
GO
BEGIN TRAN
UPDATE [Test2] SET ID2 = 1 WHERE ID2 = 100
SELECT * FROM [Test1];

Go back to you first session, and run:-

SELECT * FROM [Test2];

One of the process will be chosen as the deadlock victim and you will receive an email advising that a deadlock occurred in your database. You can then query your table to get the XML, save it as a .XDL file and view the deadlock graph.

REFERENCES
http://technet.microsoft.com/en-us/library/ms178080.aspx
http://technet.microsoft.com/en-us/library/ms166029(v=sql.90).aspx
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/