SQL Server 默认跟踪（Default Trace）

season22

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

• 本文所涉及的内容（Contents）
  
• 背景（Contexts）
  
• 基础知识（Rudimentary Knowledge）
  
• 查看默认跟踪信息（Default Trace）
  
• 补充说明（Addon）
  
• 参考文献（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:   
-- Description:   
-- Blog:        
-- =============================================
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)