SQL Event tracing (DeadLocks)

By | September 17, 2020

— SCRIPT TO LOG DEADLOCK GRAPHS USING SERVER SIDE TRACE
— It is configured to run the rest of the day after it is started and is limited to max 2*10MB log files
— Make sure the SQL user account have write access to the trace log output folder
— The output .trc files can be viewed as a graphical view in “SQL Server Profiler”

— Trace can be stopped manually with (replace x with traceid returned when starting the trace):
— sp_trace_setstatus @traceid = x, @status = 0 — Used to stop trace
— sp_trace_setstatus @traceid = x, @status = 2 — Used to delete trace
— select * from sys.fn_trace_getinfo(0) — List all server side traces

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = DateAdd(d,1,Convert(date, GetDate())) –Run for 1 day, Alternative set a specific time ‘YYYY-MM-DD HH:MM:SS.MMM’
set @maxfilesize = 10 — Max 10MB trace output files

exec @rc = sp_trace_create @TraceID output, 2, N’E:\Backup\FindDeadLocks’, @maxfilesize, @Datetime, 2 — 2 = TRACE_FILE_ROLLOVER, 2 = File count
if (@rc != 0) goto error

declare @on bit
set @on = 1
— Event 148 = Deadlock Graph Columns
exec sp_trace_setevent @TraceID, 148, 3, @on — 3 = DatabaseId
exec sp_trace_setevent @TraceID, 148, 1, @on — 1 = TextData
exec sp_trace_setevent @TraceID, 148, 14, @on — 14 = StartTime
exec sp_trace_setevent @TraceID, 148, 34, @on — 34 = ObjectName
exec sp_trace_setevent @TraceID, 148, 4, @on — 4 = TransactionId
exec sp_trace_setevent @TraceID, 148, 12, @on — 12 = SPID
exec sp_trace_setevent @TraceID, 148, 51, @on — 51 = EventSequence

exec sp_trace_setstatus @TraceID, 1 — Start trace
select TraceID=@TraceID — Return trace id
goto finish
error:
select ErrorCode=@rc
finish:
go