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