SQL Server 默认跟踪（Default Trace）
一、 背景
思考这样的场景：数据库的表、存储过程经常别修改，当这些修改造成BUG的时候，很多开发都不承认是他们干的，那我们有没办法找出谁干的呢？
SQL Server有Default Trace默认跟踪，数据库记录信息到log.trc文件，可以查看trace_event_id，46表示Create对象（Object:Created），表示Drop对象（Object:Deleted），表示Alter对象（Object:Altered）。
虽然可以通过上面的方式找到相关的操作，但是它有两个缺点：
1) log.trc文件是滚动更新文件，所有有可能会被系统删除，你找不了太久的数据；
2) 有些操作你可能是后知后觉，出了问题才会去找问题，我们应该主动去监控这些DDL；
我们可以使用DDL触发器主动监控DDL语句的执行，当有对数据库执行DDL就会触发，我们把这些信息保存到表中，并且把操作用户的HostName和修改的T-SQL以邮件的形式发送到指定的邮件。本文将讲述使用Default Trace默认跟踪解决上面的问题，DDL触发器的方式可以参考：SQL Server DDL触发器运用 和 SQL Server 数据库邮件。
二、 基础知识
默认追踪是在SQL Server 2005中首次出现的新功能，它提供了审计模式修改的功能，例如表创建、存储过程删除等类似过程。默认情况下它是运行的，但是你可以通过sp_configure来启用和停用它。
默认跟踪日志可以通过 SQL Server Profiler打开并查看，或者通过 Transact-SQL 使用 fn_trace_gettable 系统函数查询返回一个表，并且可以对表数据进行过滤、筛选。
默认跟踪能帮助我们跟踪什么有用的信息呢？你可以查看到如下几个内容：
2) 数据库中那些对象被created /altered /deleted
4) 查看、过滤Login failed for user 'sa'等错误信息
三、 查看默认跟踪信息
下面主要看看在我们日常使用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 --产生表创建事件 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），表示Drop对象（Object:Deleted），表示日志文件自动增长（Log File Auto Grow），表示Alter对象（Object:Altered），表示错误日志（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。
--生成表修改事件 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），表示Drop对象（Object:Deleted），表示日志文件自动增长（Log File Auto Grow），表示Alter对象（Object:Altered），表示错误日志（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。
--触发表删除事件 USE TraceDB GO DROP TABLE Trace_log
（Figure7：Drop事件后半部分信息）
四、 补充说明
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有什么关系嘛？
五、 参考文献
SQL Server 2005 - Default Trace (默认跟踪)
default trace enabled (Option)
fn_trace_gettable (Transact-SQL)
-------------------华丽分割线-------------------
作者：听风吹雨
版权：本文版权归作者和博客园共有
转载：欢迎转载，必须保留原文链接
格言：不喜欢是因为你不会 && 因为会所以喜欢
-------------------华丽分割线-------------------