Blocked Process Report

http://technet.microsoft.com/en-us/subscriptions/ms191168.aspx

捕获超过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/1000from
    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