I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

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.

 

kick it on DotNetKicks.com
 

Print | posted on Friday, July 18, 2008 11:00 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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
7/24/2008 8:43 PM | Arthur Fuller
Gravatar

# 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>
7/24/2008 9:02 PM | Mladen
Gravatar

# 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








8/29/2008 7:32 AM | Jayanth Nadig
Gravatar

# 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?
1/7/2009 10:16 PM | Jason Browne
Gravatar

# re: Immediate deadlock notifications without changing existing code

Fail how?
1/7/2009 11:37 PM | Mladen
Gravatar

# 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?
5/13/2009 6:26 AM | Kenny
Gravatar

# re: Immediate deadlock notifications without changing existing code

your database probably doesn't have service broker enabled. enable it.
5/13/2009 11:28 AM | Mladen
Gravatar

# 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
12/22/2009 6:46 AM | KKishore
Gravatar

# re: Immediate deadlock notifications without changing existing code

the error message says you haven't enabled your queue.
12/22/2009 10:15 AM | Mladen
Gravatar

# 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
2/16/2010 6:06 PM | Marrina Chen
Gravatar

# 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
2/26/2010 11:44 AM | Mario
Gravatar

# re: Immediate deadlock notifications without changing existing code

how do I display the deadlock graph?
10/22/2010 9:38 PM | rick wenger
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET