Updated 2010-07-24; added timeout to the receive command in the stored procedure. For more info, see Service Broker can make your transaction log big.
Event Notifications are based on Service Broker. This brings an advantage and a disadvantage; enhanced scalability at the cost of increased complexity (and lack of a User Interface, so we have to take it to T-SQL code). Lets take it there step by step for SQL Server 2005 and 2008;
- Some code to cause deadlocks.
- See how the deadlocks look like using the Trace Flags.
- See how the deadlocks look like in Profiler.
- Use the Event Notification on the deadlocks.
- Sending out the mail.
The code shown as part of this article is also available at this link. To code to create the database for the deadlocks is available at this link. The code works with SQL Server 2005 SP2 and SQL Server 2008
1. Some code to cause deadlocks.
To get the code to cause the deadlocks, click this link. Run the code from SQL Server Management Studio (SSMS) to create the DeadLockSample database. Next create two additional connections (New Query) to the DeadLockSample database, execute the procedure procLockedTran1 on one connection and procLockedTran2 on the other connection. There is a 5 seconds delay in the two procedures between locking the first and second resource. So if you execute the two procedures within 5 seconds, you get your deadlock. You could also change the delay in the procedures if you think 5 seconds is to short/long.
Normally a process would be terminated when it is the deadlock victim. For the procedures created by the script, the error is handled , so the process that is the deadlock victim just selects it is the victim and the connection will not be terminated.
If you want to create a deadlock in any of the following scenarios, just run procLockedTran1 and procLockedTran2 on two separate connections simultaneously.
2. See how the deadlocks look like using the Trace Flags.
To get the deadlocks recorded in the SQL Server Error Log, either set the trace flag as startup parameter in SQL Server Configuration Manager (see picture) which will cause the trace flag to be active each time you start your SQL Server (until you remove the trace flag again), or issue a DBCC TRACEON (1222,-1) command which sets the trace flag until you stop SQL Server or issue a DBCC TRACEOFF (1222,-1).
With the trace flag active, you can now see the deadlock appear in you SQL Server Error Log.
3. See how the deadlocks look like in Profiler.
... or in SSMS for that matter. With the SQL Server tools it is possible to display deadlock graphs; XML documents (with .xdl extension) describing the deadlock information of one or more deadlocks. When the deadlock graph event (EventClass 148) with the TextData column is included in the trace definition, the XML to create the deadlock graph is captured (both through the Profiler graphical tool and the sp_trace procedures) and the graph will automatically be displayed when opened in Profiler. Also the graphical profiler has the option to write deadlock graphs directly to a file. Note that the graphical presentation of the deadlock is limited compared to the information in the .xdl document. The information in the .xdl document is the same information as the information written to the SQL Server Error Log with trace flag 1222. One extra comment if you filter your trace on database, you won't get the deadlock graph, as noticed by Kalen Delaney.
4. Use the Event Notification on the deadlocks.
Event Notification lets you capture events that can occur on server, database or broker queue, for a list of events, query sys.event_notification_event_types. For this purpose, the event type 1148, DEADLOCK_GRAPH is of interest. Also note that the DEADLOCK_GRAPH is a trace event, which can only be captured at server level.
The second thing needed is a broker SERVICE to send the event to. Since we'll be handling the events at server level, the SERVICE (and QUEUE) will be created in the msdb database. There is no need to create the MESSAGE TYPEs or CONTRACTs as they already exist for Event Notification.
USE msdb
GO
-- Create a queue to receive messages.
CREATE QUEUE queDeadLock_Graph;
GO
-- Create a service on the queue that references
-- the event notifications contract.
CREATE SERVICE svcDeadLock_Graph
ON QUEUE queDeadLock_Graph
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Now query the sys.databases for the service_broker_guid of the msdb database.
SELECT service_broker_guid FROM sys.databases WHERE name = 'msdb';
The result of the query for the Service Broker GUID for the msdb databases is needed to point to the right database for the service.
-- Create the event notification.
CREATE EVENT NOTIFICATION evnDeadLock_Graph
ON SERVER
FOR DEADLOCK_GRAPH
TO SERVICE 'svcDeadLock_Graph'
, '2A7C5300-7C64-43F5-AB10-5B079D07678E'; -- the GUID for msdb goes here.
You can look at the content of the queue through SELECT * FROM msdb.dbo.queDeadLock_Graph, as a queue in fact is a table. Next step will be processing the messages that get into the queue and more specifically get the deadlock details out of the message_body. The message body looks binary, but if you query it like this;
SELECT CAST(message_body AS xml) FROM msdb.dbo.queDeadLock_Graph
you'll notice that it is actually XML, where the textdata node holds the xdl structure of the deadlock graph.
5. Sending out the mail
Okay, we're in Service Broker land by now. Event Notification acts as the Initiator Service and the svcDeadLock_Graph is the Target Service. In this scenario, the only thing left to be done is specify how the queue should be processed. The processing shall be done by a stored procedure that will be tied to the queue as the activation program. Once something comes in to the queue, this stored procedure will be invoked... kind of like a trigger for insert on a normal table. The specification for the stored procedure will be:
- Extract the deadlock graph from the message_body and store it in a variable.
- Extract the datetime of the deadlock graph notification and store it in a variable.
- Store the datetime and deadlock graph in a table.
- Send the datetime as part of the message and deadlock graph as attachment in an email. This does require database mail to be set up, if you haven't, an error will be logged in the table.
The code to create the table to hold the deadlock information (and the reason why no mail was sent).
USE msdb
GO
CREATE TABLE dbo.tblDeadLock_Log (
DeadLock_ID int IDENTITY(1,1) CONSTRAINT pk_tblDeadLock_Log PRIMARY KEY
, DeadLock_Detected datetime
, DeadLock_Graph xml
, NoMailReason nvarchar(2048))
GO
The stored procedure to be used as the activation program on the queue, this is where the real work is done. Alter the @profile_name and @recipients in at sp_send_dbmail to match the setup of your dbmail setup.
CREATE PROCEDURE dbo.procReceiveDeadLock_Graph
AS
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
DECLARE @message_type_name nvarchar(128)
DECLARE @deadlock_graph xml
DECLARE @event_datetime datetime
DECLARE @deadlock_id int
BEGIN TRY
BEGIN TRAN
WAITFOR(
RECEIVE TOP(1) @conversation_handle = conversation_handle
, @message_body = CAST(message_body AS xml)
, @message_type_name = message_type_name
FROM dbo.queDeadLock_Graph)
, TIMEOUT 10000 -- Line added 2010-07-24;
-- /ssb/2010/07/24/ServiceBrokerCanMakeYourTransactionLogBig.aspx
-- Validate message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' AND
@message_body.exist('(/EVENT_INSTANCE/TextData/deadlock-list)') = 1)
BEGIN
-- Extract the info from the message
SELECT @deadlock_graph = @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)')
, @event_datetime = @message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
-- Put the info in the table
INSERT dbo.tblDeadLock_Log (DeadLock_Detected, DeadLock_Graph)
VALUES (@event_datetime, @deadlock_graph)
SELECT @deadlock_id = SCOPE_IDENTITY()
-- Send deadlock alert mail.
-- Requires configured database mail, will log an error if not (or anything else goes wrong).
BEGIN TRY
DECLARE @subj nvarchar(255), @bdy nvarchar(max), @qry nvarchar(max), @attfn nvarchar(255)
SELECT @subj = 'A deadlock occurred on ' + @@SERVERNAME
, @bdy = 'A deadlock occurred at ' + CONVERT(varchar(50),@event_datetime, 120) + ' on SQL Server: ' + @@SERVERNAME + '. See attached xdl-file for deadlock details.'
, @qry = 'SET NOCOUNT ON; SELECT deadlock_graph FROM msdb.dbo.tblDeadLock_Log WITH (READUNCOMMITTED) WHERE DeadLock_ID = ' + CAST(@deadlock_id AS varchar(10)) -- Locking hint is to prevent this dynamic query to be blocked by the lock held by the insert. The dynamic SQL will not come from inside this transaction.
, @attfn = @@SERVERNAME + '_' + CAST(@deadlock_id AS varchar(10)) + '.xdl'
EXEC sp_send_dbmail @profile_name = 'default'
, @recipients = 'administrator@localhost'
, @subject = @subj
, @body = @bdy
, @query = @qry
, @attach_query_result_as_file = 1
, @query_attachment_filename = @attfn -- http://support.microsoft.com/kb/924345
, @query_result_header = 0
, @query_result_width = 32767
, @query_no_truncate = 1
END TRY
BEGIN CATCH
UPDATE dbo.tblDeadLock_Log
SET NoMailReason = ERROR_MESSAGE()
WHERE DeadLock_ID = @deadlock_id
END CATCH
END
ELSE -- Not an event notification with deadlock-list
END CONVERSATION @conversation_handle
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO
Finally to tie the stored procedure to the queue so incoming notifications will be processed.
ALTER QUEUE dbo.queDeadLock_Graph
WITH
STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = msdb.dbo.procReceiveDeadLock_Graph,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)
GO
Now try the procedures procLockedTran1 and procLockedTran2 and see the results in the msdb.dbo.tblDeadLock_Log and in your mail (if configured).