Sqlserver——日常维护——追踪事件的脚本方法

本次例子,追踪系统的阻塞的语句和进程，想要追踪其他事件类(如：存储过程、批处理语句)可以参考联机丛书

可以在sqlserver 代理任务中执行该段语句，监视每天指定时间生产环境阻塞情况分析

--------------------------创建跟踪,跟踪数据库运行中的阻塞情况------------------------
BEGIN
   
--------------1、开启数据库的高级选项  
EXEC sys.sp_configure 'show advanced options', 1 ;  
RECONFIGURE ;   

--------------2、设定阻塞的阈值,本次设置为10S,超过10S判断为阻塞,写入到阻塞报告中 
EXEC sys.sp_configure 'blocked process threshold', 10 ;  
RECONFIGURE ;  

--------------3、创建跟踪文件
--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'F:\Sqlserver跟踪文件\XXX4'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=5
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5

exec @rc=sp_trace_Create
@TraceID= @TraceID output,
@[email protected],
@[email protected],
@[email protected],
@[email protected],
@[email protected]


--------------4、设置跟踪文件跟踪的事件类(阻塞类的事件ID为137),以及相关详细信息(TextData列为1)[email protected]代表跟踪ID,@EventID-代表事件类ID,@Column_id-代表追踪事件指定列的信息
EXEC sys.sp_trace_setevent @[email protected],@eventid=137,@column_id=1,@on=1


--------------5、设置跟踪文件的跟踪状态为[email protected] 为前一步创建好的跟踪ID,statue为1-代表启用,0-代表禁用,2-代表删除该跟踪文件
EXEC sys.sp_trace_setstatus @[email protected],@statue=1


--------------6、设置监视的时间，例子为10S，可以实际环境设置
WAITFOR DELAY '00:00:10'


--------------7、关闭跟踪状态
EXEC sys.sp_trace_setstatus @TraceID,0


--------------8、将跟踪日志写入到日志表中查看
DECLARE @TableName NVARCHAR(100)='BlockLog_'+CONVERT(NVARCHAR(20),GETDATE(),112)
DECLARE @SQL NVARCHAR(MAX)='SELECT * INTO '[email protected]+' FROM ::fn_trace_gettable('+CHAR(39)[email protected]+'.trc'+CHAR(39)+',1)'


EXEC sys.sp_executesql @SQL


--------------9、删除跟踪文件
EXEC sys.sp_trace_setstatus @TraceID,2


END




           

执行完后可以按照日志表的格式，查看指定的阻塞日志信息

Eg:select * from BlockLog_20190227