捕捉一個SQL Server會話的所有語句

問題：有一件事讓我感到特別沮喪，那就是我經常寫一些複雜的查詢，但是我經常忘記儲存它們或者不記得在我運行五個迭代之前的查詢是什麼樣的。一個明智的做法是一直儲存著你的指令碼，但是當你處在嘗試做一堆不同的事情時，你總有可能忘記了某些事情。在本技巧中，我們看看怎樣建立一個會話的伺服器端跟蹤並且完整地捕捉所有啟動並執行指令碼，這樣你可以找到你認為可能會丟失的複雜查詢。

　　首先需要做的是建立兩個預存程序，在這個技巧中，我在自己的主要資料庫上建立它們。它們允許你建立一個伺服器端的跟蹤，也允許你關掉一個伺服器端的跟蹤。要瞭解更多關於伺服器端跟蹤的資訊，請參考前面的技巧“使用一個伺服器端得跟蹤進行SQL
Server 效能統計”。

　　開始跟蹤

　　第一個預存程序顯示如下，它啟動了伺服器端的跟蹤，但是也排除了你想捕捉的特定會話的SPID。它將建立一個檔案名稱，如“TraceMySession_52_d20090317120912.trc”，以便該跟蹤會話是唯一的。預設情況下，它儲存在伺服器的"C:"盤下，因此你可以把它更改到另一個不同的目錄下。建立這個指令碼也是為了建立一個5MB的檔案然後復原到另外的檔案中。你可以再次檢驗上面提到的技巧得到更多關於這些設定的資訊。

　　我們正在做的另一件事是只捕捉SQL:BatchCompleted事件。用這種方法這個追蹤檔案不會變得非常大。捕捉的三個資料欄位是TextData，StartTime和SPID。

　　要建立這個預存程序，複製下面的代碼並且執行它。正如我所說的，我在主要資料庫中建立這個程式，但是你也可以在不同的資料庫中建立它。

　　

CREATE PROCEDURE [dbo].[spTraceMySessionStart] @spid INT 　　AS 　　-- Create a Queue 　　DECLARE @rc INT 　　DECLARE @TraceID INT 　　DECLARE @maxfilesize bigint 　　SET @maxfilesize = 5 　　DECLARE @filename NVARCHAR(245) 　　SET @filename = 'C:\TraceMySession_' 　　+ CONVERT(NVARCHAR(10),@spid) 　　+ '_d' 　　+ REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') 　　+ REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 　　EXEC @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, NULL 　　IF (@rc != 0) GOTO error 　　-- Set the events 　　DECLARE @on bit 　　SET @on = 1 　　EXEC sp_trace_setevent @TraceID, 12, 1, @on 　　EXEC sp_trace_setevent @TraceID, 12, 12, @on 　　EXEC sp_trace_setevent @TraceID, 12, 14, @on 　　-- Set the Filters 　　DECLARE @intfilter INT 　　DECLARE @bigintfilter bigint 　　EXEC sp_trace_setfilter @TraceID, 12, 1, 0, @spid 　　-- Set the trace status to start 　　EXEC sp_trace_setstatus @TraceID, 1 　　-- display trace id for future references 　　SELECT TraceID=@TraceID 　　GOTO finish 　　error: 　　SELECT ErrorCode=@rc 　　finish: 　　停止跟蹤 　　一旦你完成了跟蹤，那麼下面的這個預存程序將停止和關掉它。如果這不能運行，那麼伺服器端的跟蹤將會繼續運行和搜集其它任何正好具有相同SPID的會話資料。 　　這個預存程序排除一個參數，那就是TraceID。當你正好在運行第一個預存程序時，它將給你建立的TraceID。這是你傳遞到第二個預存程序以便停滯和關掉該跟蹤的值。 　　我也把它建立在自己的主要資料庫中，但是同樣地你可以建立在任何一個不同的資料庫中。      CREATE PROCEDURE [dbo].[spTraceMySessionStop] @traceId INT 　　AS 　　EXEC sp_trace_setstatus @traceId,0 　　EXEC sp_trace_setstatus @traceId,2 讓我們試試看 　　因此我們可以假設，我們想運行下面的代碼。      EXEC master.dbo.spTraceMySessionStart 52 　　USE AdventureWorks 　　GO 　　SELECT name 　　FROM sys.sysobjects WHERE xtype = 'U' 　　GO 　　SELECT TOP 10 Title 　　FROM HumanResources.Employee 　　GO 　　EXEC master.dbo.spTraceMySessionStop 2 首先我們需要做的是啟動這個跟蹤，也就是我指令碼中的第一行。我可以在底部看到我的SPID，也就是52，它已經在下面的中反白了。 　　 　　圖一 　　當我運行這個預存程序時，輸出結果顯示的是TraceID，也就是“2”。我們需要儲存這個值以便後面使用。 　　 　　圖二 　　在我運行了其它的命令並且全部完成之後，我可以運行後面的命令來停止和關掉這個跟蹤並使用我們上面得到的TraceID值“2”。 　　 　　圖三 　　查看我們所捕捉的 　　如果我使用探查器開啟我們建立的檔案，我可以看到在我的會話中啟動並執行所有命令。你可以把這些命令複製到一個查詢時段以便重複使用。 　　 　　圖四 　　另一個選擇是下載一個建立到SQL Server表的檔案或者使用SQL Server只查詢這些資料。 　　 　　圖五      SELECT TEXTData, StartTime 　　FROM ::fn_trace_gettable('c:\TraceMySession_52_d20090317120542.trc', DEFAULT) 　　WHERE TEXTData IS NOT NULL