Blocked Process Report

最新推荐文章于 2021-05-26 15:23:51 发布

yq495

最新推荐文章于 2021-05-26 15:23:51 发布

阅读量357

点赞数

分类专栏： SQL SERVER 文章标签： report go output system null file

SQL SERVER 专栏收录该内容

17 篇文章

订阅专栏

本文介绍了如何使用SQL Server配置和系统跟踪功能来捕获并记录超过10秒的进程阻塞情况，包括配置阈值、事件记录和停止追踪的方法。

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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

原文链接：http://www.cnblogs.com/stswordman/archive/2010/08/02/1790291.html