In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error.
The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and
it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :)
SQL Server 2005 Event notifications
Event notifications are a special kind of database object that sen:d information about server and database events to a Service Broker service.
They execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information
about these events to a Service Broker service. There are three scopes for event notifications: Server, Database and Queue.
We of course want a Server wide deadlock notification so that we can be notified of all deadlocks on the entire server
I have to point out that event notification are an awesome use of Service Broker functionality.
Setup
For the purpose of this post I've used tempdb to hold our deadlock event info. Of course this should go into an administrative database if you have one.
Also an email is sent to notify the DBA that the deadlock happened. Thus the Immediate part :)
USE tempdb
GO
-- this procedure will write our event data into the table and send the notification email
CREATE PROCEDURE usp_ProcessNotification
AS
DECLARE @msgBody XML
DECLARE @dlgId uniqueidentifier
-- you can change this to get all messages at once
WHILE(1=1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- receive messages from the queue one by one
;RECEIVE TOP(1)
@msgBody = message_body,
@dlgId = conversation_handle
FROM dbo.DeadLockNotificationsQueue
-- exit when the whole queue has been processed
IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
BREAK;
END
-- insert event data into our table
INSERT INTO TestEventNotification(eventMsg)
SELECT @msgBody
DECLARE @MailBody NVARCHAR(MAX)
SELECT @MailBody = CAST(@msgBody AS NVARCHAR(MAX));
-- send an email with the defined email profile.
-- since this is async it doesn't halt execution
-- EXEC msdb.dbo.sp_send_dbmail
-- @profile_name = 'your mail profile', -- your defined email profile
-- @recipients = 'dba@yourCompany.com', -- your email
-- @subject = 'Deadlock occured notification',
-- @body = @MailBody;
IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- write any error in to 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 WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
DATABASE Name: %s; Error number: %I64d; Error Message: %s',
16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG;
END CATCH;
END
GO
-- create the notification queue that will receive the event notification messages
-- add the activation stored procedure that will process the messages in the queue
-- as they arrive
CREATE QUEUE DeadLockNotificationsQueue
WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = usp_ProcessNotification,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo' );
GO
-- crete the notofication service for our queue with the pre-defined message type
CREATE SERVICE DeadLockNotificationsService
ON QUEUE DeadLockNotificationsQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
WITH SERVICE_NAME = 'DeadLockNotificationsService',
ADDRESS = 'LOCAL';
GO
-- create the event notification for the DEADLOCK_GRAPH event.
-- other lock events can be added
CREATE EVENT NOTIFICATION DeadLockNotificationEvent
ON SERVER
FOR DEADLOCK_GRAPH -- , LOCK_DEADLOCK_CHAIN, LOCK_DEADLOCK, LOCK_ESCALATION -- ANY OF these can be SET
TO SERVICE 'DeadLockNotificationsService',
'current database' -- CASE sensitive string that specifies USE OF server broker IN CURRENT db
GO
-- check to see if our event notification has been created ok
SELECT * FROM sys.server_event_notifications WHERE name = 'DeadLockNotificationEvent';
GO
-- create the table that will hold our deadlock info
CREATE TABLE TestEventNotification(Id INT IDENTITY(1,1), EventMsg xml, EventDate datetime default(GETDATE()))
GO
-- clean up
/*
DROP TABLE TestEventNotification
DROP PROCEDURE usp_ProcessNotification
DROP EVENT NOTIFICATION DeadLockNotificationEvent ON SERVER
DROP ROUTE DeadLockNotificationsRoute
DROP SERVICE DeadLockNotificationsService
DROP QUEUE DeadLockNotificationsQueue
*/
Testing
For testing you'll need to open 2 windows in SQL Server Management Studio
-- tun this first to create the test table
USE AdventureWorks
IF object_id('DeadlockTest') IS NOT NULL
DROP TABLE DeadlockTest
GO
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest
SELECT 1 UNION ALL
SELECT 2
GO
----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 1
BEGIN TRAN
UPDATE DeadlockTest
SET id = 12
WHERE id = 2
-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'
UPDATE DeadlockTest
SET id = 11
WHERE id = 1
COMMIT
----------------------------------------------------------------
----------------------------------------------------------------
-- run this in query window 2 a second or two
-- after you've run the script in query window 1
BEGIN TRAN
UPDATE DeadlockTest
SET id = 11
WHERE id = 1
-- wait 5 secs to set up deadlock condition in other window
WAITFOR DELAY '00:00:05'
UPDATE DeadlockTest
SET id = 12
WHERE id = 2
COMMIT
----------------------------------------------------------------
----------------------------------------------------------------
-- run this after the test to see that we have our deadlock event notification saved
USE tempdb
SELECT * FROM TestEventNotification
ORDER BY id
We can see that this setup works great. Because we have subscribed to the DEADLOCK_GRAPH event we can see the same information
as if we had traced it with the SQL Profiler. Of course this kind of setup can be used for any kind of event that is supported.