一.本文所涉及的內(nèi)容（Contents）

1. 本文所涉及的內(nèi)容（Contents）
2. 背景（Contexts）
3. 基礎(chǔ)知識（Rudimentary Knowledge）
4. 查看默認(rèn)跟蹤信息（Default Trace）
5. 補(bǔ)充說明（Addon）
6. 參考文獻(xiàn)（References）

二.背景（Contexts）

思考這樣的場景：數(shù)據(jù)庫的表、存儲過程經(jīng)常別修改，當(dāng)這些修改造成BUG的時候，很多開發(fā)都不承認(rèn)是他們干的，那我們有沒辦法找出誰干的呢？

創(chuàng)新互聯(lián)建站提供高防服務(wù)器、云服務(wù)器、香港服務(wù)器、成都移動服務(wù)器托管等

SQL Server有Default Trace默認(rèn)跟蹤，數(shù)據(jù)庫記錄信息到log.trc文件，可以查看trace_event_id，46表示Create對象（Object:Created），47表示Drop對象（Object:Deleted），93表示日志文件自動增長（Log File Auto Grow），164表示Alter對象（Object:Altered），20表示錯誤日志（Audit Login Failed）。

雖然可以通過上面的方式找到相關(guān)的操作，但是它有兩個缺點(diǎn)：

1) log.trc文件是滾動更新文件，所有有可能會被系統(tǒng)刪除，你找不了太久的數(shù)據(jù)；

2) 有些操作你可能是后知后覺，出了問題才會去找問題，我們應(yīng)該主動去監(jiān)控這些DDL；

我們可以使用DDL觸發(fā)器主動監(jiān)控DDL語句的執(zhí)行，當(dāng)有對數(shù)據(jù)庫執(zhí)行DDL就會觸發(fā)，我們把這些信息保存到表中，并且把操作用戶的HostName和修改的T-SQL以郵件的形式發(fā)送到指定的郵件。本文將講述使用Default Trace默認(rèn)跟蹤解決上面的問題，DDL觸發(fā)器的方式可以參考：SQL Server DDL觸發(fā)器運(yùn)用 和 SQL Server 數(shù)據(jù)庫郵件。

三.基礎(chǔ)知識（Rudimentary Knowledge）

默認(rèn)追蹤是在SQL Server 2005中首次出現(xiàn)的新功能，它提供了審計(jì)模式修改的功能，例如表創(chuàng)建、存儲過程刪除等類似過程。默認(rèn)情況下它是運(yùn)行的，但是你可以通過sp_configure來啟用和停用它。

默認(rèn)跟蹤日志可以通過 SQL Server Profiler打開并查看，或者通過 Transact-SQL 使用 fn_trace_gettable 系統(tǒng)函數(shù)查詢返回一個表，并且可以對表數(shù)據(jù)進(jìn)行過濾、篩選。

默認(rèn)跟蹤能幫助我們跟蹤什么有用的信息呢？你可以查看到如下幾個內(nèi)容：

1) 使用Default Trace查看誰還原了你的數(shù)據(jù)庫

2) 數(shù)據(jù)庫中那些對象被created /altered /deleted

3) 查找日志文件快速增長的原因

4) 查看、過濾Login failed for user 'sa'等錯誤信息

四.查看默認(rèn)跟蹤信息（Default Trace）

下面主要看看在我們?nèi)粘Ｊ褂肈DL的過程中，默認(rèn)跟蹤會記錄些什么東西：

(一) 檢查Default Trace是否已經(jīng)開啟，如果返回Figure1中value為1，那就說明已經(jīng)開啟默認(rèn)跟蹤了；如果value為0表示關(guān)閉默認(rèn)跟蹤；

--查詢Default Trace是否開啟 SELECT * FROM sys.configurations WHERE configuration_id = 1568;

（Figure1：default trace enabled信息）

(二) 如果默認(rèn)跟蹤是關(guān)閉的，可以通過下面的方式進(jìn)行開啟和測試：

--開啟Default Trace sp_configure 'show advanced options' , 1 ;GO RECONFIGURE;GO sp_configure'default trace enabled' , 1 ;GO RECONFIGURE;GO --測試是否開啟 EXEC sp_configure 'default trace enabled';GO --關(guān)閉Default Trace sp_configure 'default trace enabled' , 0 ;GO RECONFIGURE;GO sp_configure'show advanced options' , 0 ;GO RECONFIGURE;GO

(三) 獲取當(dāng)前正在使用的log.trc滾動更新文件的路徑：

--獲取當(dāng)前跟蹤文件的路徑 SELECT * FROM ::fn_trace_getinfo(0)

（Figure2：log.trc文件路徑）

選項(xiàng)property值代表的意義：

1：trace options，有2（滾動文件）、4、8（黑盒）三個值，請參考sp_trace_create；

2：file name，更準(zhǔn)確來說是trace文件的路徑；

3：max file size，設(shè)置最大滾動文件大小，當(dāng)達(dá)到這個值就會創(chuàng)建新的滾動文件；

4：stop time，設(shè)置trace停止的時間；

5：當(dāng)前狀態(tài)(0=stopped, 1=running) ；

SQL Server2000中，使用fn_trace系列系統(tǒng)存儲過程時，需要在存儲過程名前加"::"標(biāo)識；SQL Server2000中，僅當(dāng)跟蹤被停止(stop)并關(guān)閉(close)后，跟蹤的內(nèi)容才會寫入文件中；

(四) 下面測試默認(rèn)跟蹤是如何跟蹤最常使用的DDL腳本的。首先創(chuàng)建一個測試數(shù)據(jù)庫TraceDB，再創(chuàng)建一個測試表Trace_log，通過下面的腳本，默認(rèn)跟蹤記錄了Figure3和Figure4的內(nèi)容，EventName為Object:Created。

--創(chuàng)建測試數(shù)據(jù)庫 USE MASTERGO CREATE DATABASE TraceDB--通過創(chuàng)建表產(chǎn)生一個DDL事件 USE TraceDBGO CREATE TABLE dbo.Trace_log(
  IdINT IDENTITY(1,1) not null,
  SometextCHAR(3) null )--Script1：返回剛剛Create操作的信息 -- ============================================= -- Author:        <聽風(fēng)吹雨> -- Create date:    <2013.05.03> -- Description:    <讀取、過濾log.trc文件> -- Blog:        <http://www.cnblogs.com/gaizai/> -- ============================================= DECLARE @tracefile NVARCHAR(MAX)SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\log.trc' FROM sys.traces WHERE [is_default] = 1)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(@tracefile, DEFAULT) gtLEFT 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以內(nèi)的spid為系統(tǒng)使用 gt.[DatabaseName] = 'TraceDB' AND --根據(jù)DatabaseName過濾 gt.[ObjectName] = 'Trace_log' AND --根據(jù)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

（Figure3：Create事件前半部分信息）

（Figure4：Create事件后半部分信息）

(五) 接著測試修改表所產(chǎn)生的事件跟蹤日志，首先我們?nèi)藶榈纳梢粋€修改表的事件，為Trace_log表添加一列，把上面的Script1腳本W(wǎng)here的e.[trace_event_id] = 46替換為e.[trace_event_id] = 164，這樣就可以查看Alter對象的信息，EventName為Object:Altered。

--通過修改表產(chǎn)生一個DDL事件 USE TraceDBGO ALTER TABLE Trace_logADD Col INT --Script2：返回剛剛Alter操作的信息 WHERE gt.[spid] > 50 AND --50y以下的為系統(tǒng)使用 gt.[DatabaseName] = 'TraceDB' AND --根據(jù)DatabaseName過濾 gt.[ObjectName] = 'Trace_log' AND --根據(jù)objectname過濾 e.[category_id] = 5 AND --category 5表示對象，表示安全 e.[trace_event_id] = 164 --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

（Figure5：Alter事件前半部分信息）

（Figure6：Alter事件后半部分信息）

(六) 接著測試修改表所產(chǎn)生的事件跟蹤日志，首先我們?nèi)藶榈纳梢粋€刪除表的事件，再把上面的Script1腳本W(wǎng)here的e.[trace_event_id] = 46替換為e.[trace_event_id] = 47，這樣就可以查看Drop對象的信息，EventName為Object: Deleted。

--通過刪除表產(chǎn)生一個DDL事件 USE TraceDBGO DROP TABLE Trace_log

（Figure7：Drop事件后半部分信息）

五.補(bǔ)充說明（Addon）

1. 對于log.trc文件，好像只保留5個文件，什么地方可以設(shè)置？文件的大小默認(rèn)為20MB，有沒地方可以設(shè)置？SQL Server只會維護(hù)5個Trace文件，最大為20M。當(dāng)SQL Server重新啟動或者達(dá)到最大值之后會生成新的文件，將最早的Trace文件刪除。

（Figure8：log*.trc文件）

（Figure9：log*.trc設(shè)置）

嘗試使用下面SQL對系統(tǒng)表進(jìn)行更新失?。篹xec sp_configure 'allow updates',1

此選項(xiàng)仍然存在于 sp_configure 存儲過程中，但是其功能在 SQL Server 中不可用。 其設(shè)置不起作用。 從 SQL Server 2005 開始，不支持直接更新系統(tǒng)表。

2. 雙擊log.trc文件會以SQL Server Profiler方式打開，看到這里是不是有熟悉的感覺了？對的只不過我們平時使用Profiler是自定義跟蹤事件，而保存在Log文件夾中的這些是系統(tǒng)默認(rèn)進(jìn)行跟蹤的。

3. 除了使用SQL Server Profiler自定義跟蹤之外，還可以使用系統(tǒng)存儲過程：sp_trace_create、sp_trace_setevent等的T-SQL來創(chuàng)建跟蹤，詳情請參考：SQL 跟蹤簡介。

4. 關(guān)于fn_trace_gettable系統(tǒng)函數(shù)的參數(shù)，有必要在這里講講，為了看到不同參數(shù)對讀取文件的影響，這里使用下面的SQL腳本進(jìn)行測試，返回COUNT(1) 查看讀取文件的差異性。

1) 以@tracefile文件作為起始，往后讀取1個滾動更新文件，1為這個文件本身；

2) 以@tracefile文件作為起始，往后讀取2個滾動更新文件；

3) 以@tracefile文件作為起始，0、-1、default都是表示往后讀取所有文件；

--定義文件路徑變量 DECLARE @tracefile NVARCHAR(MAX)SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\log.trc' FROM sys.traces WHERE [is_default] = 1)--以@tracefile文件作為起始，往后讀取1個滾動更新文件，1為這個文件本身 SELECT COUNT(1) FROM ::fn_trace_gettable(@tracefile,1)--以@tracefile文件作為起始，往后讀取2個滾動更新文件 SELECT COUNT(1) FROM ::fn_trace_gettable(@tracefile,2)--以@tracefile文件作為起始，0、-1、default都是表示往后讀取所有文件 SELECT COUNT(1) FROM ::fn_trace_gettable(@tracefile,0)SELECT COUNT(1) FROM ::fn_trace_gettable(@tracefile,-1)SELECT COUNT(1) FROM ::fn_trace_gettable(@tracefile,default)

5. Default Trace不能代替DDL trigger的功能（參考：SQL Server 使用DDL Trigger防止數(shù)據(jù)庫修改）。默認(rèn)跟蹤應(yīng)被用作SQL實(shí)例的監(jiān)視器，或用來快速獲得SQL問題事件的詳細(xì)信息。

6. Default Trace不會跟蹤所有的事件，它撲捉一些關(guān)鍵性信息，包括auditing events，database events，error events，full text events，object creation，object deletion，object alteration。

7. 在Read Default Trace中描述了關(guān)于trace_event_id的信息：If you are interested in what the default trace has been setup to capture you can run this (Note you cannot edit the default trace!)。

--Script5：trace_event SELECT * FROM fn_trace_geteventinfo(1) tgINNER JOIN sys.trace_events te ON tg.[eventid] = te.[trace_event_id] INNER JOIN sys.trace_columns tc ON tg.[columnid] = tc.[trace_column_id] WHERE te.name like '%login%'

（Figure10：trace_event_id信息）

另外查看Event類型的方式還可以通過：sp_trace_setevent。

8. 關(guān)于Script1腳本：FROM fn_trace_gettable(@tracefile, DEFAULT) gt中@tracefile變量表示跟蹤日志文件路徑的寫法，還可以使用下面的方式，但是有點(diǎn)需要注意，下面的方式返回的是當(dāng)前正在使用的滾動更新文件開始查找，而Script1的是以歷史滾動第一個文件開始查找。

--當(dāng)前滾動更新文件 FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150),(SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL)f WHERE f.property= 2 )),DEFAULT) gt

9. 如何獲取某個Trace跟蹤了哪些Event和column呢？

--獲取某個Trace跟蹤了哪些Event和column DECLARE @traceid INT SET @traceid = 1 SELECT TCA.category_id,TCA.name AS category_name
    ,TE.trace_event_id,TE.nameAS trace_event_name
    ,TCO.trace_column_id,TCO.nameAS trace_column_nameFROM fn_trace_geteventinfo(@traceid) AS EILEFT JOIN sys.trace_events AS TEON EI.eventid = TE.trace_event_idLEFT JOIN sys.trace_categories AS TCAON TE.category_id = TCA.category_idLEFT JOIN sys.trace_columns AS TCOON EI.columnid = TCO.trace_column_idGO

（Figure11：某Trace信息）

10. DBCC TRACEON (xxx);這種跟蹤標(biāo)記和Default Trace有什么關(guān)系嘛？

六.參考文獻(xiàn)（References）

SQL Server 2005 - Default Trace (默認(rèn)跟蹤)

使用Default Trace查看誰還原了你的數(shù)據(jù)庫？

The Default Trace

default trace enabled (Option)

SQL SERVER跟蹤功能

Trace 的一些另類的應(yīng)用

Read Default Trace

fn_trace_gettable

fn_trace_gettable (Transact-SQL)

sp_trace_setevent

ObjectType Trace Event Column

SQL 跟蹤簡介

如何使用存儲的過程來監(jiān)視 SQL Server 2005 中的跟蹤

sp_trace_create (Transact-SQL)

網(wǎng)頁標(biāo)題：SQLServer默認(rèn)跟蹤（DefaultTrace）
文章網(wǎng)址：http://jinyejixie.com/article32/pgsisc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、云服務(wù)器、全網(wǎng)營銷推廣、響應(yīng)式網(wǎng)站、、小程序開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)