SQL Server deadlock instant notification without modifying the existing Code

Post by: Mladen prajdi IK: immediate deadlock configurations without changing existing code

In myPrevious postAbout immediate deadlock events 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 EventsAre 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 usedTempdbTo hold our deadlock event info. Of course this shocould go into an administrative database if you have one.

Also an email is sent to inform y the DBA that the deadlock happened. Thus the immediate part

Use tempdbgo -- this procedure will write our event data into the table and send the notification emailcreate 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 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 processing ing service broker message from queue deadlocknotificationsqueue. database Name: % s; error number: % i64d; error message: % s', 16, 1, @ dbname, @ errornumber, @ errormessage) with log; end catch; endgo -- 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 arrivecreate queue deadlocknotifsqusqueue 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 typecreate service deadlocknotifsservice on queue deadlocknotifsqusqueue ([response); go -- create the route for the servicecreate route deadlocknotifsroute with SERVICE_NAME = 'adlocknotificationsservice', address = 'local'; go -- create the event notification for the deadlock_graph event. -- Other lock events can be added create Event Notification deadlocknotificationeventon server for deadlock_graph --, lock_deadlock_chain, lock_deadlock, lock_escalation -- any these can be setto service 'deadlocknotificationsservice ', 'Current database' -- Case Sensitive string that specifies use of server broker in current dbgo -- check to see if our event notification has been created okselect * From sys. server_event_configurications where name = 'destlocknotificationevent'; go -- create the table that will hold our deadlock infocreate table testeventnotification (ID int identity (), eventmsg XML, eventdate datetime default (getdate () Go -- clean up/* Drop table testeventicationicationdrop procedure usp_processicationdrop drop Event Notification deadlockicationicationevent on server drop route Processing Service Queue queue deadlockicationsqusqueue */

Testing

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

-- Tun this first to create the test table use adventureworksif object_id ('destlocktest') is not null drop table deadlocktest gocreate table deadlocktest (id int) insert into deadlocktestselect 1 Union allselect 2go conditions --- run this in query window 1 begin tranupdate deadlocktest Set ID = 12 where id = 2 -- Wait 5 secs to set up deadlock condition in other unknown wwaitfor delay' 00: 00: 05 'Update deadlocktest Set ID = 11 where id = 1commit success --- run this in query window 2 a second or two -- after you 've run the script in query window 1 begin tranupdate deadlocktest set id = 11 where id = 1 -- Wait 5 secs to set up deadlock condition in other login wwaitfor delay '00: 00: 05 'Update deadlocktest Set ID = 12 where id = 2commit success --- run this after the test to see that we have our deadlock Event Notification saveduse tempdbselect * From testeventicationicationorder 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.