Handling SQL Server Deadlocks With Event Notifications
- Posted in:
- Database Administration
- SQL Server

I've seen a few different methods for handling deadlocks in SQL Server. I don't notice the use of event notifications very often, so I thought I'd share my approach. For my purposes, I want SQL to notify me when a deadlock occurs. I also want an easily accessible deadlock graph that I can inspect and potentially pass along to development or software vendors.
Let's skip ahead to the end result. Here's a glimpse at my Outlook Inbox. The selected email is an alert I received when a deadlock occurred.
There's a small amount of info in the email body that provides some basic information (name of the SQL host, time the deadlock occurred, etc.) There's also a file attachment with the *.xdl file extension, which tells us it is a "Microsoft SQL Server Deadlock File". It contains a wealth of information for the SPIDs that were involved in the deadlock. If you open the file in a text editor like Notepad.exe, you'll see the data is stored in XML format. But it's not easy to parse and interpret it as-is. So instead, open the file with SSMS and view the information graphically. Here's what it looks like:
T-SQL Automation
To automatically generate those emails/alerts, I created four objects (you can read more about them in this post):
- A QUEUE.
- A SERVICE.
- An EVENT NOTIFICATION for the DEADLOCK_GRAPH event.
- An activation PROCEDURE for the QUEUE.
Below is a script that creates the objects listed above--it's in the first tab. It's important to be familiar with the format for the XML data returned by the EVENTDATA() function for the DEADLOCK_GRAPH event--that's in the second tab. There's also some sample EVENTDATA() data in the third tab. I generated it with this deadlock example:
--Create a queue just for Deadlock events. CREATE QUEUE queDeadlockNotification --Create a service just for Deadlock events. CREATE SERVICE svcDeadlockNotification ON QUEUE queDeadlockNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) -- Create the event notification for Deadlock events on the service. CREATE EVENT NOTIFICATION enDeadlock ON SERVER WITH FAN_IN FOR DEADLOCK_GRAPH TO SERVICE 'svcDeadlockNotification', 'current database'; GO CREATE PROCEDURE dbo.ReceiveDeadlock /****************************************************************************** * Name : dbo.ReceiveDeadlock * Purpose : Handles deadlock events (activated by QUEUE queDeadlockNotification) * Inputs : None * Outputs : None * Returns : Nothing ****************************************************************************** * Change History * 06/26/2017 DMason Created ******************************************************************************/ AS BEGIN SET NOCOUNT ON DECLARE @MsgBody XML WHILE (1 = 1) BEGIN BEGIN TRANSACTION -- Receive the next available message FROM the queue WAITFOR ( RECEIVE TOP(1) -- just handle one message at a time @MsgBody = CAST(message_body AS XML) FROM queDeadlockNotification ), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away -- If we didn't get anything, bail out IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION BREAK END ELSE BEGIN --Do stuff here. DECLARE @Login SYSNAME = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)' ); DECLARE @IsSystem INT = @MsgBody.value('(/EVENT_INSTANCE/IsSystem)[1]', 'VARCHAR(8)' ); DECLARE @StartTime VARCHAR(128) = @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ); DECLARE @Start DATETIME = @StartTime DECLARE @FileName NVARCHAR(255)= 'Deadlock Graph ' + REPLACE(CONVERT(VARCHAR, @Start, 121), ':', '.') + '.xdl'; DECLARE @MailBody NVARCHAR(MAX); DECLARE @Subject NVARCHAR(255); DECLARE @Qry NVARCHAR(MAX); SET @Qry = CAST(@MsgBody.query('/EVENT_INSTANCE/TextData/deadlock-list') AS NVARCHAR(MAX)); SET @Qry = REPLACE(@Qry, CHAR(39), CHAR(39) + CHAR(39)); SET @Qry = 'SET NOCOUNT ON; ' + CHAR(13) + CHAR(10) + 'SELECT ' + CHAR(39) + @Qry + CHAR(39); SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' ) SET @MailBody = '<table border="1">' + '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + '<tr><td>Start Time</td><td>' + @StartTime + '</td></tr>' + '<tr><td>Login Name</td><td>' + @Login + '</td></tr>' + '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + '<tr><td>Is System</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/IsSystem)[1]', 'VARCHAR(128)' ) + '</td></tr>' + '</table><br/>'; EXEC msdb.dbo.sp_send_dbmail @recipients = 'You@YourDomain.com', @subject = @Subject, @body = @MailBody, @body_format = 'HTML', @query = @Qry, @attach_query_result_as_file = 1, @query_attachment_filename = @FileName, @query_no_truncate = 1, @query_result_width = 32767, @exclude_query_output = 1 /* Commit the transaction. At any point before this, we could roll back -- the received message would be back on the queue AND the response wouldn't be sent. */ COMMIT TRANSACTION END END END GO ALTER QUEUE dbo.queDeadlockNotification WITH STATUS = ON, ACTIVATION ( PROCEDURE_NAME = dbo.ReceiveDeadlock, STATUS = ON, MAX_QUEUE_READERS = 1, EXECUTE AS OWNER) GO
<xs:complexType name="EVENT_INSTANCE_DEADLOCK_GRAPH"> <xs:sequence> <!-- Basic Envelope --> <xs:element type="SSWNAMEType" name="EventType"/> <xs:element type="xs:string" name="PostTime"/> <xs:element type="xs:int" name="SPID"/> <!-- Main Body --> <xs:element type="xs:anyType" name="TextData"/> <xs:element type="emptiableLong" name="TransactionID"/> <xs:element type="SqlTraceNameType" name="LoginName"/> <xs:element type="xs:string" name="StartTime"/> <xs:element type="SqlTraceNameType" name="ServerName"/> <xs:element type="xs:base64Binary" name="LoginSid"/> <xs:element type="xs:long" name="EventSequence"/> <xs:element type="emptiableInt" name="IsSystem"/> <xs:element type="SqlTraceNameType" name="SessionLoginName"/> </xs:sequence> </xs:complexType>
<EVENT_INSTANCE> <EventType>DEADLOCK_GRAPH</EventType> <PostTime>2017-06-26T14:28:15.780</PostTime> <SPID>22</SPID> <TextData> <deadlock-list> <deadlock victim="process80574988"> <process-list> <process id="process80574988" taskpriority="0" logused="216" waitresource="RID: 2:1:7360:0" waittime="6790" ownerId="678928045" transactionname="user_transaction" lasttranstarted="2017-06-26T14:28:01.773" XDES="0xb6ce3970" lockMode="U" schedulerid="2" kpid="1640" status="suspended" spid="183" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-26T14:28:08.997" lastbatchcompleted="2017-06-26T14:28:01.773" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DMasonHP" hostpid="12604" loginname="Celtics\CMaxwell" isolationlevel="read committed (2)" xactid="678928045" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x0200000005db68127d85eb887f1be301338b2f2ebaa91f6e"> UPDATE [##Suppliers] set [Fax] = @1 WHERE [supplierid]=@2 </frame> <frame procname="adhoc" line="1" sqlhandle="0x02000000ca059406a0c94311928b17b2b824c0d847e40b7f"> UPDATE ##Suppliers SET Fax = N'555-1212' WHERE supplierid = 1; </frame> </executionStack> <inputbuf> UPDATE ##Suppliers SET Fax = N'555-1212' WHERE supplierid = 1; </inputbuf> </process> <process id="process134e43b88" taskpriority="0" logused="224" waitresource="RID: 2:3:4016:0" waittime="2844" ownerId="678928065" transactionname="user_transaction" lasttranstarted="2017-06-26T14:28:05.670" XDES="0x14ff82e90" lockMode="U" schedulerid="1" kpid="4704" status="suspended" spid="182" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-26T14:28:12.930" lastbatchcompleted="2017-06-26T14:28:05.673" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DMasonHP" hostpid="12604" loginname="Celtics\CMaxwell" isolationlevel="read committed (2)" xactid="678928065" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000005f889415278064e941bfe9fe107454d8d8caa4c4"> UPDATE [##Employees] set [phone] = @1 WHERE [empid]=@2 </frame> <frame procname="adhoc" line="1" sqlhandle="0x020000008d2d0c2d462424d968b40ec2f7564ea3d34b2790"> UPDATE ##Employees SET phone = N'555-9999' WHERE empid = 1; </frame> </executionStack> <inputbuf> UPDATE ##Employees SET phone = N'555-9999' WHERE empid = 1; </inputbuf> </process> </process-list> <resource-list> <ridlock fileid="1" pageid="7360" dbid="2" objectname="tempdb.dbo.##Suppliers" id="lock19e1e7b00" mode="X" associatedObjectId="7998393042331172864"> <owner-list> <owner id="process134e43b88" mode="X" /> </owner-list> <waiter-list> <waiter id="process80574988" mode="U" requestType="wait" /> </waiter-list> </ridlock> <ridlock fileid="3" pageid="4016" dbid="2" objectname="tempdb.dbo.##Employees" id="lockf69fdd80" mode="X" associatedObjectId="7926335448252284928"> <owner-list> <owner id="process80574988" mode="X" /> </owner-list> <waiter-list> <waiter id="process134e43b88" mode="U" requestType="wait" /> </waiter-list> </ridlock> </resource-list> </deadlock> </deadlock-list> </TextData> <TransactionID /> <LoginName>sa</LoginName> <StartTime>2017-06-26T14:28:15.777</StartTime> <ServerName>DMasonHP</ServerName> <LoginSid>AQ==</LoginSid> <EventSequence>189164</EventSequence> <IsSystem>1</IsSystem> <SessionLoginName /> </EVENT_INSTANCE>
Activation Procedure Notes
The XML data returned by the EVENTDATA() function that was written to the QUEUE is assigned to the @MsgBody variable:
RECEIVE TOP(1) -- just handle one message at a time @MsgBody = CAST(message_body AS XML) FROM queDeadlockNotification
The <deadlock-list> element and all of its data is assigned to the @Qry variable. This data will become the contents of a file attachment for an email message.
SET @Qry = CAST(@MsgBody.query('/EVENT_INSTANCE/TextData/deadlock-list') AS NVARCHAR(MAX));
Next, we create a "query" that does nothing more than select the <deadlock-list> data as a string literal. This "query" will be the @query parameter value for msdb.dbo.sp_send_dbmail. The REPLACE function is used to double up any single quotes that may exist.
SET @Qry = REPLACE(@Qry, CHAR(39), CHAR(39) + CHAR(39)); SET @Qry = 'SET NOCOUNT ON; ' + CHAR(13) + CHAR(10) + 'SELECT ' + CHAR(39) + @Qry + CHAR(39);
Pros And Cons
As noted, there are other ways to handle deadlocks in SQL Server. The approach presented here may have some drawbacks compared to others. There is an authorization issue for msdb.dbo.sp_send_dbmail that will need to be addressed for logins without elevated permissions. Additionally, you might get hit with an unexpected deluge of emails. (The first time I got deadlock alerts, there were more than 500 of them waiting for me in my Inbox.) Lastly, there's the XML issue: it's not everyone's cup of tea. On the plus side, I really like the proactive nature: an event occurs, I get an email. I think most would agree it's better to know something (bad) happened before the customers start calling. The automated generation of Deadlock Graph (*.xdl) files is convenient. And event notifications have been available since SQL Server 2005. As far as I know, the feature is available in all editions, including Express Edition.
Comments