Mladen Prajdić Blog

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

Immediate deadlock notifications without changing existing code

by Mladen Prajdić in  

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



 


Testing


For testing you'll need to open 2 windows in SQL Server Management Studio




 


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?