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