SQL Server中關於跟蹤（Trace)那點事

SQL Server中關於跟蹤（Trace)那點事

前言

一提到跟蹤倆字，很多人想到警匪片中的情境，同樣在我們的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檔案路徑，根據它我們可以找到其它的追蹤檔案，預設是同一目錄下

第三個：表示滾動檔案的大小（單位MB），當到達這個值就會建立新的滾動檔案

第四個：跟蹤的停止時間，這裡為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 將刪除該功能，改用擴充事件。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁