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.
|
|
Source Click Here.
Post a Comment