SQL Server 2005 - Default Trace (預設跟蹤)

在我們的工作中可能會遇到這樣一種情形。由于資料庫中某些對象被altered/created/deleted，造成我們的應用程式crash。

當我們把問題解決之後，老闆可能會問發生了什麼？為什麼會這樣？是誰幹的？

在SQL Server 2005中提供了DDL trigger，它能回答所用這些問題，但我們沒有在事前實作這一工作。

在這種情況下，有些人可能會對老闆說：“我不知道，但我能去查。”之後就瘋狂地尋找第三方的工具來讀transaction log（事物日志）。他們可能不知道SQL Server 2005有一個内置的功能能回到所用這些問題。

答案藏在一個運作在背景的跟蹤（Default Trace）中，我們可以利用Default Trace來尋找排除故障。Default Trace預設會自動運作，因為其非常輕量級，是以不會給系統造成很多負擔。對我來說，Default Trace所帶來的益處遠大于它給資料庫帶來的負擔。Default Trace不能代替DDL trigger的功能，它應被用作SQL執行個體的螢幕，或用來快速獲得SQL問題事件的詳細資訊。

Default Trace不會跟蹤所有的事件，它撲捉一些關鍵性資訊，包括auditing events，database events，error events，full text events，object creation，object deletion，object alteration。在本文中我們會關注對象級（event level）的事件，這能回答老闆的“是誰幹的”這一問題。

下面的代碼能查詢Default Trace是否已經被開啟了

select * from sys.configurations where configuration_id = 1568

如果Default Trace功能沒有打開，我們先要打開這個Trace。可以是使用如下代碼，注意運作這些代碼需要相應的權限。

sp_configure 'show advanced options', 1;

go

reconfigure;

go

sp_configure 'default trace enabled', 1;

go

reconfigure;

go

下一段代碼是來擷取目前跟蹤檔案的路徑。在相應的檔案夾下可能會有多個以log_開頭的trace檔案，我們可以把這些檔案載入一個表中，進行分析，但載入多個檔案會增加開銷。最好隻是載入需要分析的資訊。

select * from ::fn_trace_getinfo(0)

現在讓我們看一看跟蹤下來的資料。我們先從建立一個新資料庫開始。

use master

go

create database TraceDB 

使用如下的代碼來打開跟蹤檔案，擷取建立資料庫的資訊。這裡使用了一些限制性的條件來縮小結果集。

注意：你的跟蹤檔案路徑和名稱可能和我的不同。要使用你自己的跟蹤路徑。

select

loginname,

loginsid,

spid,

hostname,

applicationname,

servername,

databasename,

objectname,

e.category_id,

cat.name as [CategoryName],

textdata,

starttime,

eventclass,

eventsubclass, --0表示begin，1表示commit

e.name as EventName

from ::fn_trace_gettable('C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/log.trc',0)

inner join sys.trace_events e

on eventclass = trace_event_id

inner join sys.trace_categories as cat

on e.category_id = cat.category_id

where databasename = 'TraceDB' and

objectname is null and --根據objectname來過濾

e.category_id = 5 and --category 5表示對象

e.trace_event_id = 46 --trace_event_id: 46表示Create對象，47表示Drop對象，164表示修改對象 

你能看到超過一條的結果，這是因為捕捉到了2事件子類，begin和commit。

現在建立另一個對象并再次進行查詢。這次我們建立一張表“MyTable”。

use TraceDB

go

create table dbo.MyTable(

id int identity(1,1) not null,

sometext char(3) null

) on primary

現在使用類似的腳本來查詢Default Trace，對where條件部分進行修改。

where databasename = 'TraceDB' and

objectname = 'MyTable' and

e.category_id = 5 and

e.trace_event_id = 46

我們同樣可以修改對象，代碼如下：

use TraceDB

go

alter table MyTable

add col int

使用如下的條件來過濾查詢，特别注意修改trace_event_id為164，因為164代表對象修改事件

where databasename = 'TraceDB' and

objectname = 'MyTable' and

e.category_id = 5 and

e.trace_event_id = 164

最後我們可以把"MyTable"Drop掉。

use TraceDB

go

drop table MyTable

使用如下的條件來過濾查詢，注意修改trace_event_id為47，因為47代表對象删除事件

where databasename = 'TraceDB' and

objectname = 'MyTable' and

e.category_id = 5 and

e.trace_event_id = 47

Default Trace還能跟蹤到其他一些事件。例如你的日志檔案快速增長，這時需要知道其原因。Default Trace會捕獲日志增長事件，這對于排查問題很有價值。下面的查詢會獲得Default Trace中所有的log auto growth事件。

select

loginname,

loginsid,

spid,

hostname,

applicationname,

servername,

databasename,

objectname,

e.category_id,

cat.name,

textdata,

starttime,

endtime,

duration,

eventclass,

eventsubclass,

e.name as EventName

from ::fn_trace_gettable('C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/log.trc',0)

inner join sys.trace_events e

on eventclass = trace_event_id

inner join sys.trace_categories as cat

on e.category_id = cat.category_id

where databasename = 'TraceDB' and

e.category_id = 2 and --categroy 2表示database

e.trace_event_id = 93 --93表示日志檔案自動增長事件