Post date: Sep 30, 2011 9:00:40 AM
You can monitor for deadlocks in your system, but the Deadlock graph will only show the latest queries run in the two transactions involved. You cannot see what other queries have been run in those transactions before the deadlock. This can make it hard to figure out where the deadlock comes from.
To know what statements have been run before the deadlock occurred you need to do a trace. Tracing a busy system can generate a lot of data and you cannot really store all that trace so you need to use ROLLOVER option in your trace files. To avoid overwriting the trace data containing the transactions involved in the deadlocks we need to automatically stop the trace when the deadlock occurs.
Setup the Trace
declare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @maxfilesize = 200exec @rc = sp_trace_create @TraceID output, 2, N'e:\tracedata\Deadlocktrace', @maxfilesize, NULL, 5 if (@rc != 0) goto error -- Set the eventsdeclare @on bitset @on = 1-- 148 = Deadlock graph-- exec sp_trace_setevent @TraceID, 148, 11, @onexec sp_trace_setevent @TraceID, 148, 51, @on --EventSequenceexec sp_trace_setevent @TraceID, 148, 4, @on --TransactionIDexec sp_trace_setevent @TraceID, 148, 12, @on --SPIDexec sp_trace_setevent @TraceID, 148, 14, @on --StartTimeexec sp_trace_setevent @TraceID, 148, 1, @on --TextData-- 10 = RPC:Completedexec sp_trace_setevent @TraceID, 10, 15, @on --EndTimeexec sp_trace_setevent @TraceID, 10, 1, @on --TextDataexec sp_trace_setevent @TraceID, 10, 9, @on --ClientProcessIDexec sp_trace_setevent @TraceID, 10, 51, @on --EventSequenceexec sp_trace_setevent @TraceID, 10, 4, @on --TransactionIDexec sp_trace_setevent @TraceID, 10, 12, @on --SPIDexec sp_trace_setevent @TraceID, 10, 14, @on --StartTime-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigint-- SQL text not like exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%spam%' -- 7 = NOT LIKEexec sp_trace_setfilter @TraceID, 1, 0, 7, N'%unprepare%' -- 7 = NOT LIKE(thanks for the spam hibernate)set @intfilter = 6 -- DB IDexec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finish error: select ErrorCode=@rcfinish: -- select * from sys.traces-- select id from sys.traces where path like 'e:\tracedata\Deadlocktrace%'-- select * from sys.trace_events where name like '%completed%'-- select * from sys.trace_columns where name like '%client%'--sp_trace_setstatus @traceid = @TraceID , @status = 1 -- start--sp_trace_setstatus @traceid = 2 , @status = 0 -- stop --sp_trace_setstatus @traceid = 2 , @status = 2 -- Delete definition from server
Create Event Notification using service broker to stop the trace.
NOTE, Service broker need to be enabled for the database.
NOTE, The database need to be set as TRUSTWORTHY using (ALTER DATABASE CustomerDB SET TRUSTWORTHY ON;) to allow the automatic stopping of the trace using the sp_trace_setstatus procedure.
If you do not want to set it as TRUSTWORTHY then you can create a certificate and sign the "StopthatTraceNOW" procedure with it. Search the internet for more information about signing procedures and creating certificates.
CREATE TABLE petersTraceTable ( service_instance_id UNIQUEIDENTIFIER, handle UNIQUEIDENTIFIER, message_sequence_number BIGINT, service_name NVARCHAR(512), service_contract_name NVARCHAR(256), message_type_name NVARCHAR(256), validation NCHAR, message_body VARBINARY(MAX)) ;CREATE PROCEDURE StopthatTraceNOW AS DECLARE @tracenummer INT BEGIN TRY DECLARE @procTable TABLE( service_instance_id UNIQUEIDENTIFIER, handle UNIQUEIDENTIFIER, message_sequence_number BIGINT, service_name NVARCHAR(512), service_contract_name NVARCHAR(256), message_type_name NVARCHAR(256), validation NCHAR, message_body VARBINARY(MAX)) ; ;RECEIVE TOP (1) conversation_group_id, conversation_handle, message_sequence_number, service_name, service_contract_name, message_type_name, validation, message_body FROM dbo.NotifyQueue INTO @procTable INSERT petersTraceTable SELECT * FROM @procTable SELECT @tracenummer = id FROM sys.traces WHERE path LIKE 'e:\tracedata\Deadlocktrace%' EXEC sp_trace_setstatus @traceid = @tracenummer , @status = 0 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;GO --ALTER DATABASE CustomerDB SET TRUSTWORTHY ON;/*drop ROUTE NotifyRoutedrop SERVICE NotifyServicedrop QUEUE NotifyQueuedrop EVENT NOTIFICATION log_ddl1 on server*/--Create a queue to receive messages.CREATE QUEUE NotifyQueue WITH STATUS = ON, ACTIVATION(PROCEDURE_NAME = StopthatTraceNOW, MAX_QUEUE_READERS = 1, EXECUTE AS '');GO--Create a service on the queue that references--the event notifications contract.CREATE SERVICE NotifyService ON QUEUE NotifyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);GO--Create a route on the service to define the address --to which Service Broker sends messages for the service.CREATE ROUTE NotifyRoute WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL';GO--Create the event notification.CREATE EVENT NOTIFICATION log_ddl1 ON SERVER FOR DEADLOCK_GRAPH --,LOCK_DEADLOCK,LOCK_DEADLOCK_CHAINTO SERVICE 'NotifyService', 'current database'; --SELECT * FROM sys.server_event_notifications