Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

Immediate deadlock notifications without changing existing code

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 send 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 :)

<!–

Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/

–>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

</span><span style="color: #008080;">--</span><span style="color: #008080;"> you can change this to get all messages at once</span><span style="color: #008080;">

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

        </span><span style="color: #008080;">--</span><span style="color: #008080;"> exit when the whole queue has been processed</span><span style="color: #008080;">

IF @@ROWCOUNT = 0 BEGIN IF @@TRANCOUNT > 0 BEGIN ROLLBACK; END
BREAK; END

        </span><span style="color: #008080;">--</span><span style="color: #008080;"> insert event data into our table </span><span style="color: #008080;">

INSERT INTO TestEventNotification(eventMsg) SELECT @msgBody

        </span><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@MailBody</span><span style="color: #000000;"> </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">)    
        </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #008000;">@MailBody</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CAST</span><span style="color: #000000;">(</span><span style="color: #008000;">@msgBody</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">));

        </span><span style="color: #008080;">--</span><span style="color: #008080;"> send an email with the defined email profile. </span><span style="color: #008080;">

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()

        </span><span style="color: #0000FF;">RAISERROR</span><span style="color: #000000;"> (N</span><span style="color: #FF0000;">&#39;</span><span style="color: #FF0000;">Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
                    DATABASE Name: %s; Error number: %I64d; Error Message: %s</span><span style="color: #FF0000;">&#39;</span><span style="color: #000000;">, 
                    </span><span style="color: #800000; font-weight: bold;">16</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">, </span><span style="color: #008000;">@dbName</span><span style="color: #000000;">, </span><span style="color: #008000;">@errorNumber</span><span style="color: #000000;">, </span><span style="color: #008000;">@errorMessage</span><span style="color: #000000;">) </span><span style="color: #0000FF;">WITH</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">LOG</span><span style="color: #000000;">;
    </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> CATCH;
</span><span style="color: #0000FF;">END</span><span style="color: #000000;">

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

<!–

Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/

–> 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.

 

kick it on DotNetKicks.com
 

Legacy Comments


Arthur Fuller
2008-07-24
re: Immediate deadlock notifications without changing existing code
I have been following your stuff about deadlocks and learning quite a bit on the way. One thing that you seem not to touch on, though (unless I missed it) is that I would really like to know the victim's name as well. After all I've learned from reading your stuff so far, I'm sure there's a way to grab the victim's name as well as the victor. That would help enormously.

Thanks for the lessons. I'm learning a lot.
Arthur

Mladen
2008-07-24
re: Immediate deadlock notifications without changing existing code
glad you like it :)

if you take a look at the event xml you'll see that there's a node called TextData.
<TextData>
<deadlock-list>
<deadlock victim="process68b6a8"> <- SEE HERE THIS ID IDENTIFIES THE VICTIM
<process-list>
<process id="process68b4c8" ...>
...
</process>
<process id="process68b6a8" ...> <- THIS IS THE VICTIM
...
</process>
</process-list>
...
</deadlock>
</deadlock-list>
</TextData>

Jayanth Nadig
2008-08-29
re: Immediate deadlock notifications without changing existing code
Hi,

need to send email alert when the database is locked by one/more database connections, for more than a configured duration.

The following would be the criteria / sample scenarios where i need an alert:
a. More than 'x' number of (say, row-level) locks are held by a user request / SPID for 'y' minutes. The alert job could poll every 'z' minutes and keep track of which SPIDs are holding more than 'x' locks. For now, assume x is 1000, y is 5, z is 1. This way we would get an alert if an SPID held more than 1000 locks for more than 5 minutes.
b. 1 critical lock (e.g. database or table exclusive lock) is held for 'x' minutes. Even a single such lock could be trouble.

i followed your post. i did creating ProcessNotification in one window upto drop table. after that i did CREATE TABLE DeadlockTest ( id INT) in one more window. then i did UPDATE DeadlockTest SET id = 12 WHERE id = 2 in one more window.

then i ran UPDATE DeadlockTest SET id = 11 WHERE id = 1.

finally i ran SELECT * FROM TestEventNotification ORDER BY id in one more window.

but when i ran SELECT * FROM TestEventNotification ORDER BY id i am not getting recore count. what might be the problem.

Please help me.

Thanks and Regards,

Jayanth Nadig









Jason Browne
2009-01-07
re: Immediate deadlock notifications without changing existing code
I tried to use this method outside of the tempdb, but it would fail.

If I use it as is, then it works.

any suggestions?

Mladen
2009-01-07
re: Immediate deadlock notifications without changing existing code
Fail how?

Kenny
2009-05-13
re: Immediate deadlock notifications without changing existing code
This is a cool feature, but why this method doesn't return anything if we aren't putting it in tempdb? Any idea?

Mladen
2009-05-13
re: Immediate deadlock notifications without changing existing code
your database probably doesn't have service broker enabled. enable it.

KKishore
2009-12-22
re: Immediate deadlock notifications without changing existing code
Hi
this nice to trace deadlocks. But, i getting issue when any deadlock is happened the QUEUE is going to disable and errorlog incresing with below error continues until drop the QUEUE

2009-12-22 11:09:07.15 spid22s Error: 50000, Severity: 16, State: 1.
2009-12-22 11:09:07.15 spid22s Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
DATABASE Name: tempdb; Error number: 9617; Error Message: The service queue "DeadLockNotificationsQueue" is currently disabled.
2009-12-22 11:09:07.15 spid22s The activated proc '[dbo].[usp_ProcessNotification]' running on queue 'tempdb.dbo.DeadLockNotificationsQueue' output the following: 'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
DATABASE Name: tempdb; Error number: 9617; Error Message: The service queue "DeadLockNotificationsQueue" is currently disabled.'
2009-12-22 11:09:07.20 spid22s Error: 50000, Severity: 16, State: 1.
2009-12-22 11:09:07.20 spid22s Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.
DATABASE Name: tempdb; Error number: 9617; Error Message: The service queue "DeadLockNotificationsQueue" is currently disabled.

can you help me

Mladen
2009-12-22
re: Immediate deadlock notifications without changing existing code
the error message says you haven't enabled your queue.

Marrina Chen
2010-02-16
re: Immediate deadlock notifications without changing existing code
Hi,

Many thanks for your excellent example and completed procedures to handle the deadlock notification throught service broker. It worked beautifully when I used the Tempdb, but it will not work when I used a database with enabled service borker other than tempd.

Since I set up email on the stored procedure, without email received, I could not know what the error was either. Please advice and help me.

Sincerely,
Marrina

Mario
2010-02-26
re: Immediate deadlock notifications without changing existing code
Hi!

I got the same problem, but i found the answer:


ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON

That helped for me

Thank you very much for this code, very helpful indeed!

Sincerely
Mario

rick wenger
2010-10-22
re: Immediate deadlock notifications without changing existing code
how do I display the deadlock graph?