931giorni trascorsi da
SQLBits 9

powered by

Progetti‎ > ‎

Trace deadlock

use master
go

create proc dbo.stp_profile_deadlock (
@trace_path nvarchar(200), 
@trace_file nvarchar(45) = 'deadlock',
@stop_after_days int = 1,
@max_file_size bigint = 15
)
as

declare @rc int
declare @trace_id int
declare @trace_path_file nvarchar(245)
declare @date_time datetime


if @stop_after_days is null
begin
print 'The value of the parameter @stop_after_days is not valid!';
goto finish;
end;

set @max_file_size = 15;
set @trace_path = rtrim(ltrim(@trace_path));
if right(@trace_path, 1) <> '\'
set @trace_path = @trace_path + '\';

set @trace_path_file = @trace_path + 'deadlock' + '-' + CONVERT(varchar, getdate(), 112);
set @date_time = CONVERT(varchar, dateadd(dd, @stop_after_days, getdate()), 112) 


exec @rc = sp_trace_create @trace_id output, 2, @trace_path_file, @max_file_size, @date_time 
if (@rc != 0) goto error


declare @on bit
set @on = 1
exec sp_trace_setevent @trace_id, 148, 11, @on
exec sp_trace_setevent @trace_id, 148, 51, @on
exec sp_trace_setevent @trace_id, 148, 4, @on
exec sp_trace_setevent @trace_id, 148, 12, @on
exec sp_trace_setevent @trace_id, 148, 14, @on
exec sp_trace_setevent @trace_id, 148, 26, @on
exec sp_trace_setevent @trace_id, 148, 1, @on
exec sp_trace_setevent @trace_id, 59, 55, @on
exec sp_trace_setevent @trace_id, 59, 32, @on
exec sp_trace_setevent @trace_id, 59, 56, @on
exec sp_trace_setevent @trace_id, 59, 1, @on
exec sp_trace_setevent @trace_id, 59, 21, @on
exec sp_trace_setevent @trace_id, 59, 25, @on
exec sp_trace_setevent @trace_id, 59, 41, @on
exec sp_trace_setevent @trace_id, 59, 57, @on
exec sp_trace_setevent @trace_id, 59, 14, @on
exec sp_trace_setevent @trace_id, 59, 22, @on
exec sp_trace_setevent @trace_id, 59, 26, @on
exec sp_trace_setevent @trace_id, 59, 58, @on
exec sp_trace_setevent @trace_id, 59, 3, @on
exec sp_trace_setevent @trace_id, 59, 35, @on
exec sp_trace_setevent @trace_id, 59, 51, @on
exec sp_trace_setevent @trace_id, 59, 4, @on
exec sp_trace_setevent @trace_id, 59, 12, @on
exec sp_trace_setevent @trace_id, 59, 60, @on


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

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

error: 
select ErrorCode=@rc

finish: 
go


exec master.dbo.stp_profile_deadlock 
@trace_path = 'C:\PerfLogs\Admin\', 
@trace_file = 'deadlock',
@stop_after_days = 1,
@max_file_size = 1
go


Comments