Just Code‎ > ‎

TSQL - Trace for deadlocks and stop the trace when deadlock occured using EVENT NOTIFICATION and SERVICE BROKER

posted Sep 30, 2011, 2:00 AM by Peter Henell   [ updated Jan 25, 2012, 5:28 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 int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 200

exec @rc = sp_trace_create @TraceID output, 2, N'e:\tracedata\Deadlocktrace', @maxfilesize, NULL, 5 
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1

-- 148 = Deadlock graph
-- exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 51, @on --EventSequence
exec sp_trace_setevent @TraceID, 148, 4, @on --TransactionID
exec sp_trace_setevent @TraceID, 148, 12, @on --SPID
exec sp_trace_setevent @TraceID, 148, 14, @on --StartTime
exec sp_trace_setevent @TraceID, 148, 1, @on --TextData

-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 15, @on --EndTime
exec sp_trace_setevent @TraceID, 10, 1, @on --TextData
exec sp_trace_setevent @TraceID, 10, 9, @on --ClientProcessID
exec sp_trace_setevent @TraceID, 10, 51, @on --EventSequence
exec sp_trace_setevent @TraceID, 10, 4, @on --TransactionID
exec sp_trace_setevent @TraceID, 10, 12, @on --SPID
exec sp_trace_setevent @TraceID, 10, 14, @on --StartTime


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- SQL text not like 
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%spam%' -- 7 = NOT LIKE
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%unprepare%' -- 7 = NOT LIKE(thanks for the spam hibernate)
set @intfilter = 6 -- DB ID
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 

-- 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 NotifyRoute
drop SERVICE NotifyService
drop QUEUE NotifyQueue
drop 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_CHAIN
TO SERVICE 'NotifyService',
    'current database';
 
    
--SELECT * FROM sys.server_event_notifications

Comments