SQL Server 默认跟踪（Default Trace）

一.本文所涉及的内容（Contents）

1. 本文所涉及的内容（Contents）
2. 背景（Contexts）
3. 基础知识（Rudimentary Knowledge）
4. 查看默认跟踪信息（Default Trace）
5. 补充说明（Addon）
6. 参考文献（References）

二.背景（Contexts）

　　思考这样的场景：数据库的表、存储过程经常别修改，当这些修改造成BUG的时候，很多开发都不承认是他们干的，那我们有没办法找出谁干的呢？

　　SQL Server有Default Trace默认跟踪，数据库记录信息到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）。

　　虽然可以通过上面的方式找到相关的操作，但是它有两个缺点：

　　　　1) log.trc文件是滚动更新文件，所有有可能会被系统删除，你找不了太久的数据；

　　　　2) 有些操作你可能是后知后觉，出了问题才会去找问题，我们应该主动去监控这些DDL；

　　我们可以使用DDL触发器主动监控DDL语句的执行，当有对数据库执行DDL就会触发，我们把这些信息保存到表中，并且把操作用户的HostName和修改的T-SQL以邮件的形式发送到指定的邮件。本文将讲述使用Default Trace默认跟踪解决上面的问题，DDL触发器的方式可以参考：SQL Server DDL触发器运用 和 SQL Server 数据库邮件。

三.基础知识（Rudimentary Knowledge）

　　默认追踪是在SQL Server 2005中首次出现的新功能，它提供了审计模式修改的功能，例如表创建、存储过程删除等类似过程。默认情况下它是运行的，但是你可以通过sp_configure来启用和停用它。

　　默认跟踪日志可以通过 SQL Server Profiler打开并查看，或者通过 Transact-SQL 使用 fn_trace_gettable 系统函数查询返回一个表，并且可以对表数据进行过滤、筛选。

　　默认跟踪能帮助我们跟踪什么有用的信息呢？你可以查看到如下几个内容：

　　　　1) 使用Default Trace查看谁还原了你的数据库

　　　　2) 数据库中那些对象被created /altered /deleted

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

　　　　4) 查看、过滤Login failed for user 'sa'等错误信息

四.查看默认跟踪信息（Default Trace）

下面主要看看在我们日常使用DDL的过程中，默认跟踪会记录些什么东西：

(一) 检查Default Trace是否已经开启，如果返回Figure1中value为1，那就说明已经开启默认跟踪了；如果value为0表示关闭默认跟踪；

--查询Default Trace是否开启
SELECT * FROM sys.configurations WHERE configuration_id = 1568;

（Figure1：default trace enabled信息）

 

(二) 如果默认跟踪是关闭的，可以通过下面的方式进行开启和测试：

--开启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

--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO

 

(三) 获取当前正在使用的log.trc滚动更新文件的路径：

--获取当前跟踪文件的路径
SELECT * FROM ::fn_trace_getinfo(0)

（Figure2：log.trc文件路径）

选项property值代表的意义：

　　1：trace options，有2（滚动文件）、4、8（黑盒）三个值，请参考sp_trace_create；

　　2：file name，更准确来说是trace文件的路径；

　　3：max file size，设置最大滚动文件大小，当达到这个值就会创建新的滚动文件；

　　4：stop time，设置trace停止的时间；

　　5：当前状态(0=stopped, 1=running) ；

SQL Server2000中，使用fn_trace系列系统存储过程时，需要在存储过程名前加"::"标识；SQL Server2000中，仅当跟踪被停止(stop)并关闭(close)后，跟踪的内容才会写入文件中；

 

(四) 下面测试默认跟踪是如何跟踪最常使用的DDL脚本的。首先创建一个测试数据库TraceDB，再创建一个测试表Trace_log，通过下面的脚本，默认跟踪记录了Figure3和Figure4的内容，EventName为Object:Created。

--创建测试数据库
USE MASTER
GO
CREATE DATABASE TraceDB

--通过创建表产生一个DDL事件
USE TraceDB
GO
CREATE TABLE dbo.Trace_log(
  Id INT IDENTITY(1,1) not null,
  Sometext CHAR(3) null
)

--Script1：返回刚刚Create操作的信息
-- =============================================
-- Author:        <听风吹雨>
-- 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) 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] = 'TraceDB' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'Trace_log' 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

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

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

 

(五) 接着测试修改表所产生的事件跟踪日志，首先我们人为的生成一个修改表的事件，为Trace_log表添加一列，把上面的Script1脚本Where的e.[trace_event_id] = 46替换为e.[trace_event_id] = 164，这样就可以查看Alter对象的信息，EventName为Object:Altered。

--通过修改表产生一个DDL事件
USE TraceDB
GO
ALTER TABLE Trace_log
ADD Col INT

--Script2：返回刚刚Alter操作的信息
WHERE gt.[spid] > 50 AND --50y以下的为系统使用
    gt.[DatabaseName] = 'TraceDB' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'Trace_log' AND --根据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事件后半部分信息）

 

(六) 接着测试修改表所产生的事件跟踪日志，首先我们人为的生成一个删除表的事件，再把上面的Script1脚本Where的e.[trace_event_id] = 46替换为e.[trace_event_id] = 47，这样就可以查看Drop对象的信息，EventName为Object: Deleted。

--通过删除表产生一个DDL事件
USE TraceDB
GO
DROP TABLE Trace_log

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

五.补充说明（Addon）

1. 对于log.trc文件，好像只保留5个文件，什么地方可以设置？文件的大小默认为20MB，有没地方可以设置？SQL Server只会维护5个Trace文件，最大为20M。当SQL Server重新启动或者达到最大值之后会生成新的文件，将最早的Trace文件删除。

（Figure8：log*.trc文件）

（Figure9：log*.trc设置）

尝试使用下面SQL对系统表进行更新失败：exec sp_configure 'allow updates',1

此选项仍然存在于 sp_configure 存储过程中，但是其功能在 SQL Server 中不可用。 其设置不起作用。 从 SQL Server 2005 开始，不支持直接更新系统表。

 

2. 双击log.trc文件会以SQL Server Profiler方式打开，看到这里是不是有熟悉的感觉了？对的只不过我们平时使用Profiler是自定义跟踪事件，而保存在Log文件夹中的这些是系统默认进行跟踪的。

 

3. 除了使用SQL Server Profiler自定义跟踪之外，还可以使用系统存储过程：sp_trace_create、sp_trace_setevent等的T-SQL来创建跟踪，详情请参考：SQL 跟踪简介。

 

4. 关于fn_trace_gettable系统函数的参数，有必要在这里讲讲，为了看到不同参数对读取文件的影响，这里使用下面的SQL脚本进行测试，返回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防止数据库修改）。默认跟踪应被用作SQL实例的监视器，或用来快速获得SQL问题事件的详细信息。

 

6. Default Trace不会跟踪所有的事件，它扑捉一些关键性信息，包括auditing events，database events，error events，full text events，object creation，object deletion，object alteration。

 

7. 在Read Default Trace中描述了关于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) tg   
INNER 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. 关于Script1脚本：FROM fn_trace_gettable(@tracefile, DEFAULT) gt中@tracefile变量表示跟踪日志文件路径的写法，还可以使用下面的方式，但是有点需要注意，下面的方式返回的是当前正在使用的滚动更新文件开始查找，而Script1的是以历史滚动第一个文件开始查找。

--当前滚动更新文件
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.name AS trace_event_name
    ,TCO.trace_column_id,TCO.name AS trace_column_name
FROM fn_trace_geteventinfo(@traceid) AS EI
LEFT JOIN sys.trace_events AS TE
ON EI.eventid = TE.trace_event_id
LEFT JOIN sys.trace_categories AS TCA
ON TE.category_id = TCA.category_id
LEFT JOIN sys.trace_columns AS TCO
ON EI.columnid = TCO.trace_column_id
GO

（Figure11：某Trace信息）

 

10. DBCC TRACEON (xxx);这种跟踪标记和Default Trace有什么关系嘛？

六.参考文献（References）

SQL Server 2005 - Default Trace (默认跟踪)

使用Default Trace查看谁还原了你的数据库？

The Default Trace

default trace enabled (Option)

SQL SERVER跟踪功能

Trace 的一些另类的应用

Read Default Trace

fn_trace_gettable

fn_trace_gettable (Transact-SQL)

sp_trace_setevent

ObjectType Trace Event Column

SQL 跟踪简介

如何使用存储的过程来监视 SQL Server 2005 中的跟踪

sp_trace_create (Transact-SQL)