前言
一提到跟蹤倆字，很多人想到警匪片中的場景，同樣在我們的SQL Server數據庫中“跟蹤”也是無處不在的，如果我們利用好了跟蹤技巧，就可以針對某些特定的場景做定向分析，找出充足的證據來破案。
簡單的舉幾個應用場景：
在線生產庫為何突然宕機？數百張數據表為何不翼而飛？剛打好補丁的系統為何屢遭黑手？新添加的信息表為何頻頻丟失？某張表字段的突然更改，究竟為何人所為？這些個匿名的訪問背后，究竟是人是鬼？突然增加的增量數據，究竟是對是錯？數百兆的日志爆炸式的增長背后又隱藏著什么？這一且的背后，是應用程序的BUG還是用戶品質的缺失？
請關注本篇文章，讓我們一起利用數據庫的“跟蹤”(Trace)走進數據庫背后，查看其內部原理。
我相信如用過SQL Server數據庫的人，都會或多或少的利用過SQL PRofiler工具。這個玩意就是利用SQL Trace形成的一個圖形化操作工具，我們直接進入本篇的正題。
一.查看系統默認跟蹤信息（Default Trace）
Trace作為一個很好的數據庫追蹤工具，在SQL Server 2005中便集成到系統功能中去，并且默認是開啟的，當然我們也可以手動的關掉它，它位于sp_config配置參數中，我們可以通過以下語句查看：
select * from sys.configurations where configuration_id = 1568
我們也可以通過下面的語句找到這個跟蹤的記錄
select * from sys.traces
如果沒有開啟，我們也可以利用如下語句進行開啟，或者關閉等操作
--開啟Default Tracesp_configure 'show advanced options' , 1 ;GORECONFIGURE;GOsp_configure 'default trace enabled' , 1 ;GORECONFIGURE;GO--測試是否開啟EXEC sp_configure 'default trace enabled';GO--關閉Default Tracesp_configure 'default trace enabled' , 0 ;GORECONFIGURE;GOsp_configure 'show advanced options' , 0 ;GORECONFIGURE;GO
通過以下命令找到默認跟蹤的文件路徑
select * from ::fn_trace_getinfo(0)
以上命令返回的結果值，各個值（property）代表的含義如下：
第一個：2表示滾動文件；
第二個：表示當前使用的trace文件路徑，根據它我們可以找到其它的跟蹤文件，默認是同一目錄下
第三個：表示滾動文件的大?。▎挝籑B），當到達這個值就會創建新的滾動文件
第四個：跟蹤的停止時間，這里為Null，表示沒有固定的停止時間
第五個：當前跟蹤的狀態：0 停止；1 運行
找到該目錄，我們查看下該文件：
、
系統默認提供5個跟蹤文件，并且每一個文件默認大小都是20MB,SQL Server會自己維護這5個文件，當實例重啟的時候或者到達最大值的時候，之后會重新生成新的文件，將最早的跟蹤文件刪除，依次滾動更新。
我們通過以下命令來查看跟蹤文件中的內容：
默認的跟蹤文件，提供的跟蹤信息還是很全的，從中我們可以找到登錄人，操作信息等，上面的截圖只是包含的部分信息。我們可以利用該語句進行自己的加工，然后獲得更有用的信息。
--獲取跟蹤文件中前100行執行內容SELECT TOP 100 gt.[HostName] ,gt.[ServerName] ,gt.[DatabaseName] ,gt.[SPID] ,gt.[ObjectName] ,gt.[objecttype] [ObjectTypeID] ,sv.[subclass_name] [ObjectType] ,e.[category_id] [CategoryID] ,c.[Name] [Category] ,gt.[EventClass] [EventID] ,e.[Name] [EventName] ,gt.[LoginName] ,gt.[applicationName] ,gt.[StartTime] ,gt.[TextData] FROM fn_trace_gettable('E:/dataDefaultFileManger/MSSQL10.MSSQLSERVER/MSSQL/Log/log_1267.trc', DEFAULT) gt LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] WHERE gt.[spid] > 50 AND --50以內的spid為系統使用 gt.[DatabaseName] = 'master' AND --根據DatabaseName過濾 gt.[ObjectName] = 'fn_trace_getinfo' AND --根據objectname過濾 e.[category_id] = 5 AND --category 5表示對象，8表示安全 e.[trace_event_id] = 46 --trace_event_id --46表示Create對象（Object:Created）， --47表示Drop對象（Object:Deleted）， --93表示日志文件自動增長（Log File Auto Grow）， --164表示Alter對象（Object:Altered）， --20表示錯誤日志（Audit Login Failed）ORDER BY [StartTime] DESC
我創建了一張表，通過上面的跟蹤，可以跟蹤到該記錄的信息，根據不同的過濾信息，我們可以查詢出到跟蹤的某個庫的某個表的更改信息，包括：46創建（Created）、47刪除（Deleted）、93文件自動增長信息（Log File Auto Grow）、146修改（Alter）、20表示錯誤日志（Login Failed）
在生產環境中，以上幾個分類都是比較常用的，對定位部分問題的定位能夠在找到充分的證據可循，比如某廝將數據庫數據刪除掉了還不承認等，這里面的Login Failed信息，能夠追蹤出有那么用戶嘗試登陸過數據庫，并且失敗，如果大面積的出現這種情況，那就要謹防黑客襲擊了。
當然，這里我還可以利用SQL Server自帶的Profile工具，打開查看跟蹤文件中的內容。
這個圖像化的工具就比較熟悉了，直接打開進行篩選就可以了。
這種方式看似不錯，但是它也有本身的缺點，我們來看：
1、這5個文件是滾動更新的，而且每個文件默認最大都為20MB，并且沒有提供更改的接口，所以當文件填充完之后就會刪除掉，所以會找不到太久以前的內容；
2、本身默認的跟蹤，只是提供一些關鍵信息的追蹤，其中包括：auditing events，database events，error events，full text events，object creation，object deletion，object alteration，想要找到其它更詳細的內容，此方式可能無能為力；
3、在SQL Server2012后續版本的 Microsoft SQL Server 將刪除該功能，改用擴展事件。
二.自定義跟蹤信息（Default Trace）
根據上面SQL Server自帶的跟蹤信息有一些局限性，SQL Server為我們提供了自定義跟蹤的接口，我們可以自己定義跟蹤，充分擴展方法。
利用如下系統存儲過程，我們可以創建自定義的Trace
sp_trace_create [ @traceid = ] trace_id OUTPUT , [ @options = ] option_value , [ @tracefile = ] 'trace_file' [ , [ @maxfilesize = ] max_file_size ] [ , [ @stoptime = ] 'stop_time' ] [ , [ @filecount = ] 'max_rollover_files' ]
@traceid 系統默認分配跟蹤的ID號
@options 指定為跟蹤設置的選項，系統默認提供的幾個選項：
2表示當文件寫滿的時候，關閉當前跟蹤并創建新文件。
4表示如果不能將跟蹤寫入文件，不管什么原因導致，SQL Server則會關閉。這個可以利用此選項，追蹤問題
8制定服務器產生的最后5MB的跟蹤信息記錄由服務器保存。
@tracefile 跟蹤文件的路徑，這里可以是share的路徑
@maxfilesize 跟蹤文件的大小，單位是MB，默認不設置為5MB
@stoptime 跟蹤停止的時間，利用它我們可以定時跟蹤結束的日期
@filecount 默認生產的跟蹤文件的數量，比如默認的為5個，那就在第5個文件寫完的時候進行覆蓋第1個文件滾動
比如我們可以利用如下腳本進行創建
--創建跟蹤文件返回值declare @rc int--創建一個跟蹤句柄declare @TraceID int--創建跟蹤文件路徑declare @TraceFilePath nvarchar(500)set @TraceFilePath=N'F:/SQLTest/'--跟蹤文件的大小declare @maxfilesize bigintset @maxfilesize=5--設置停止的時間declare @EndTime datetimeset @EndTime=null--設置系統默認的操作declare @options intset @options=2--設置默認滾動文件的數目declare @filecount intset @filecount=5exec @rc=sp_trace_Create@TraceID output,@options,@TraceFilePath,@maxfilesize,@EndTime,@filecountif(@rc=0)select @TraceID
我們通過上面的跟蹤創建的過程，可以在系統自帶的默認的sys.traces中找到該跟蹤的明細
select * from sys.traceswhere id=2
通過上面的腳本，我們已經創建了一個新的跟蹤（trace），但是這個跟蹤狀態為0，也就是說還沒有運行，下面我們的步驟就是要為這個跟蹤添加事件（event)
這個也是利用SQL Server為我們提供的操作函數
sp_trace_setevent [ @traceid = ] trace_id , [ @eventid = ] event_id , [ @columnid = ] column_id , [ @on = ] on
@traceid 要修改的跟蹤的 ID號
@eventid 要打開的事件的 ID
@columnid 要為該事件添加的列的 ID
@on 表示事件狀態
其中最主要的就是時間ID，這個是SQL Server為我們提供的一些列的碼表時間值，具體值可以參考聯機叢書 sp_trace_setevent (Transact-SQL)
這里面最常用的就是：
事件號 | 事件名稱 | 說明 |
|---|---|---|
10 | RPC:Completed | 在完成了遠程過程調用 (RPC) 時發生。 |
11 | RPC:Starting | 在啟動了 RPC 時發生。 |
12 | SQL:BatchCompleted | 在完成了 Transact-SQL 批處理時發生。 |
13 | SQL:BatchStarting | 在啟動了 Transact-SQL 批處理時發生。 |
14 | Audit Login | 在用戶成功登錄到 SQL Server 時發生。 |
15 | Audit Logout | 在用戶從 SQL Server 注銷時發生。 |
16 | Attention | 在發生需要關注的事件（如客戶端中斷請求或客戶端連接中斷）時發生。 |
17 | ExistingConnection | 檢測在啟動跟蹤前連接到 SQL Server 的用戶的所有活動。 |
18 | Audit Server Starts and Stops | 在修改 SQL Server 服務狀態時發生。 |
20 | Audit Login Failed | 指示試圖從客戶端登錄到 SQL Server 失敗。 |
21 | EventLog | 指示已將事件記錄到 Windows 應用程序日志中。 |
22 | ErrorLog | 指示已將錯誤事件記錄到 SQL Server 錯誤日志中。 |
23 | Lock:Released | 指示已釋放某個資源（如頁）的鎖。 |
24 | Lock:Acquired | 指示獲取了某個資源（如數據頁）的鎖。 |
25 | Lock:Deadlock | 指示兩個并發事務由于試圖獲得對方事務擁有的資源的不兼容鎖而發生了相互死鎖。 |
26 | Lock:Cance 學習交流
熱門圖片
猜你喜歡的新聞
新聞熱點  2024-06-26 22:28:41
 2024-06-26 22:26:16
 2024-06-26 22:23:01
 2024-06-25 19:29:23
 2024-06-25 19:22:14
 2024-06-25 19:19:15
疑難解答 |
