sql server sp_trace_setevent 事件跟踪

/*
    sql server 2005 2008 遇到问题是还不能，不能及时的连接生产数据库，使用命令建立跟踪是是一种选择
    使用命令建立跟踪，比使用ui可以节省遇到系统的资源，该文档针对当时运行是还的锁，阻塞，运行语句进行跟踪
   
    ---跟踪事件=============================================================================================================
    14 Audit Login 在用户成功登录到 SQL Server 时发生。
 12 SQL:BatchCompleted 在完成了 Transact-SQL 批处理时发生。
 13 SQL:BatchStarting 在启动了 Transact-SQL 批处理时发生。
 68 Showplan Text (Unencoded) 显示所执行 Transact-SQL 语句的计划树。
 40 SQL:StmtStarting 在启动了 Transact-SQL 语句时发生。
 41 SQL:StmtCompleted 在完成了 Transact-SQL 语句时发生
 42 SP:Starting 指示启动了存储过程。
 43 SP:Completed 指示完成了存储过程。
 44 SP:StmtStarting 指示已开始执行存储过程中的 Transact-SQL 语句。
 45 SP:StmtCompleted 指示存储过程中的 Transact-SQL 语句已执行完毕。
 58 Auto Stats 指示发生了自动更新索引统计信息。
 59 Lock:Deadlock Chain 为导致死锁的每个事件而生成。
 148 Deadlock Graph 取消获取锁的尝试时发生，这是因为该尝试是死锁的一部分，并且被选为死锁牺牲品。 提供死锁的 XML 说明。
 137 Blocked Process Report 进程被阻塞的时间超过了指定的时间时发生。 不包括系统进程或正在等待未发现死锁的资源的进程。 
     请使用 sp_configure 来配置生成报表时的阈值和频率。
 25 Lock:Deadlock 指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。
 
 事件对对性能影响不大，但是系统稳定后要终止跟踪exec sp_trace_setstatus
   
    实施步骤：
        1 运行配置参数
        2 创建文件路径，放置跟踪文件d:\trace_file[一定要有足狗的空间] 默认运行3天
        3 运行创建存储过程
        4 执行过程
        5 检查是否运行

*/

 

--1 运行配置参数=============================================================================================================
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold',10 --10S收集一次阻塞信息
GO
RECONFIGURE
GO
--然后再执行
sp_configure

--2 创建文件路径，放置跟踪文件d:\trace_file=================================================================================

--3 创建过程过程============================================================================================================
DROP  PROCEDURE [dbo].[begin_montor];

CREATE PROCEDURE [dbo].[begin_montor]
  AS
DECLARE @TraceID        int             --跟踪编号
DECLARE @fileAddress    nvarchar(245)   --跟踪文件的地址加文件名
DECLARE @maxFileSize    bigint          --跟踪文件的最大文件大小
DECLARE @endDateTime    datetime        --跟踪结束的时间
DECLARE @fileCount      int             --跟踪文件的最大个数
DECLARE @result         int             --建立新跟踪的返回值
declare @v_while        int             --内部循环

--文件名加上当天的年月日
SET @fileAddress = N'd:\trace_file\' + Convert(char(8),GetDate(),112)
SET @maxFileSize = 50                        --每个跟踪文件最大50M
SET @endDateTime = GetDate() + 0.041666 * 72 --跟踪3天后结束
SET @fileCount = 1000                        --此处未使用此参数，不限定文件个数

--定义一个跟踪
EXECUTE @result = sp_trace_create @TraceID output, 2, @fileAddress, @maxFileSize, @endDateTime

IF (@result = 0)
BEGIN
  DECLARE @on   bit -- 1 sp_trace_setevent 必要参数
  SET @on = 1
  set  @v_while = 1
  while(@v_while < 65 )
  begin
 execute sp_trace_setevent @traceid,14 , @v_while,@on  
 execute sp_trace_setevent @traceid,12 , @v_while,@on  
 execute sp_trace_setevent @traceid,13 , @v_while,@on  
 --execute sp_trace_setevent @traceid,68 , @v_while,@on  
 execute sp_trace_setevent @traceid,40 , @v_while,@on  
 execute sp_trace_setevent @traceid,41 , @v_while,@on  
 --execute sp_trace_setevent @traceid,42 , @v_while,@on  
 --execute sp_trace_setevent @traceid,43 , @v_while,@on  
 --execute sp_trace_setevent @traceid,44 , @v_while,@on  
 --execute sp_trace_setevent @traceid,45 , @v_while,@on  
 --execute sp_trace_setevent @traceid,58 , @v_while,@on  
 execute sp_trace_setevent @traceid,59 , @v_while,@on  
 execute sp_trace_setevent @traceid,148, @v_while,@on  
 execute sp_trace_setevent @traceid,137, @v_while,@on  
 execute sp_trace_setevent @traceid,25 , @v_while,@on
     set @v_while=@v_while+1
  end

  --过滤数据库
  exec sp_trace_setfilter @TraceID,35,0,0,N'dbname' --选择特定数据库的跟踪
  --启用跟踪
  exec sp_trace_setstatus @TraceID, 1
  PRINT @TraceID
END
ELSE
BEGIN
  PRINT ('未能创建跟踪')
END

RETURN @TraceID --将新跟踪的编号做为存储过程的返回值
GO

--4执行过程创建跟踪========================================================================================================
exec dbo.[begin_montor]

--5检查创建创建跟踪========================================================================================================
select * from sys.traces --查询路径和状态
SELECT * FROM fn_trace_gettable(N'd:\trace_file\20130926.trc',1); --查询数据
--exec sp_trace_setstatus 6,2  --停止Trace