捕获超过10秒的堵塞,开启了trace之后,所有的超过10秒的堵塞会被记录下来(重复记录,例如scenario1的堵塞被记录下来, 过了10秒之后, scenario1会被再次记录
sp_configure ' show advanced options ' , 1 ;
GO
RECONFIGURE ;
GO
sp_configure ' blocked process threshold ' , 10 ;
GO
RECONFIGURE ;
go
select * from sys.traces
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
exec @rc = sp_trace_create
@TraceID output, 2 , N ' d:\trace\BlockedProcessTrace ' ,
@maxfilesize , NULL
-- 137解释:
-- Blocked Process Report Occurs when a process has been blocked for more than a specified amount of time. Does not --include system processes or processes that are waiting on non deadlock-detectable --resources. Use sp_configure to configure the threshold and frequency at which reports are generated.
if ( @rc = 0 )
begin
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID , 137 , 15 , @on
exec sp_trace_setevent @TraceID , 137 , 1 , @on
exec sp_trace_setevent @TraceID , 137 , 13 , @on
end
-- exec sp_trace_setfilter 2,35,0,0,N'testdb'
exec sp_trace_setstatus 2 , 1 -- start the trace
select * from sysprocesses where blocked <> 0
select cast (TextData as XML) , SPID, EndTime,
Duration / 1000 / 1000 from
fn_trace_gettable(N ' d:\trace\BlockedProcessTrace.trc ' , default )
where eventclass = 137
-- exec sp_trace_setstatus 2, 0--stop the trace
-- exec sp_trace_setstatus 2, 2--remove the trace definition but remain the trace file
sp_configure ' show advanced options ' , 1 ;
GO
RECONFIGURE ;
GO
sp_configure ' blocked process threshold ' , 10 ;
GO
RECONFIGURE ;
go
select * from sys.traces
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
exec @rc = sp_trace_create
@TraceID output, 2 , N ' d:\trace\BlockedProcessTrace ' ,
@maxfilesize , NULL
-- 137解释:
-- Blocked Process Report Occurs when a process has been blocked for more than a specified amount of time. Does not --include system processes or processes that are waiting on non deadlock-detectable --resources. Use sp_configure to configure the threshold and frequency at which reports are generated.
if ( @rc = 0 )
begin
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID , 137 , 15 , @on
exec sp_trace_setevent @TraceID , 137 , 1 , @on
exec sp_trace_setevent @TraceID , 137 , 13 , @on
end
-- exec sp_trace_setfilter 2,35,0,0,N'testdb'
exec sp_trace_setstatus 2 , 1 -- start the trace
select * from sysprocesses where blocked <> 0
select cast (TextData as XML) , SPID, EndTime,
Duration / 1000 / 1000 from
fn_trace_gettable(N ' d:\trace\BlockedProcessTrace.trc ' , default )
where eventclass = 137
-- exec sp_trace_setstatus 2, 0--stop the trace
-- exec sp_trace_setstatus 2, 2--remove the trace definition but remain the trace file