SQL Server中关于跟踪（Trace)那点事

一提到跟踪俩字，很多人想到警匪片中的场景，同样在我们的SQL Server数据库中ldquo;跟踪rdquo;也是无处不在的，如果我们利用好

前言

一提到跟踪俩字，很多人想到警匪片中的场景，同样在我们的SQL Server数据库中“跟踪”也是无处不在的，如果我们利用好了跟踪技巧，就可以针对某些特定的场景做定向分析，找出充足的证据来破案。

简单的举几个应用场景：

在线生产库为何突然宕机？数百张数据表为何不翼而飞？刚打好补丁的系统为何屡遭黑手？新添加的信息表为何频频丢失？某张表字段的突然更改，究竟为何人所为？这些个匿名的访问背后，究竟是人是鬼？突然增加的增量数据，究竟是对是错？数百兆的日志爆炸式的增长背后又隐藏着什么？这一且的背后，是应用程序的BUG还是用户品质的缺失？

请关注本篇文章，让我们一起利用数据库的“跟踪”(Trace)走进数据库背后，查看其内部原理。 

我相信如用过SQL Server数据库的人，都会或多或少的利用过SQL Profiler工具。这个玩意就是利用SQL Trace形成的一个图形化操作工具，我们直接进入本篇的正题。 

一.查看系统默认跟踪信息（Default Trace）

Trace作为一个很好的数据库追踪工具，在SQL Server 2005中便集成到系统功能中去，并且默认是开启的，当然我们也可以手动的关掉它，它位于sp_config配置参数中，我们可以通过以下语句查看：

select * from sys.configurations where configuration_id = 1568

我们也可以通过下面的语句找到这个跟踪的记录

select * from sys.traces

如果没有开启，我们也可以利用如下语句进行开启，或者关闭等操作

--开启Default Trace sp_configure , 1 ; GO RECONFIGURE; GO sp_configure , 1 ; GO RECONFIGURE; GO --测试是否开启 EXEC sp_configure ; GO --关闭Default Trace sp_configure , 0 ; GO RECONFIGURE; GO sp_configure , 0 ; GO RECONFIGURE; GO

通过以下命令找到默认跟踪的文件路径

select * from ::fn_trace_getinfo(0)

以上命令返回的结果值，各个值（property）代表的含义如下：

第一个：2表示滚动文件；

第二个：表示当前使用的trace文件路径，根据它我们可以找到其它的跟踪文件，，默认是同一目录下

第三个：表示滚动文件的大小（单位MB），当到达这个值就会创建新的滚动文件

第四个：跟踪的停止时间，这里为Null，表示没有固定的停止时间

第五个：当前跟踪的状态：0 停止；1 运行

 

找到该目录，我们查看下该文件：

、

系统默认提供5个跟踪文件，并且每一个文件默认大小都是20MB,SQL Server会自己维护这5个文件，当实例重启的时候或者到达最大值的时候，之后会重新生成新的文件，将最早的跟踪文件删除，依次滚动更新。

 

我们通过以下命令来查看跟踪文件中的内容：

 默认的跟踪文件，提供的跟踪信息还是很全的，从中我们可以找到登录人，操作信息等，上面的截图只是包含的部分信息。我们可以利用该语句进行自己的加工，然后获得更有用的信息。

--获取跟踪文件中前100行执行内容 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(, 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] = AND --根据DatabaseName过滤 gt.[ObjectName] = 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

 我创建了一张表，通过上面的跟踪，可以跟踪到该记录的信息，根据不同的过滤信息，我们可以查询出到跟踪的某个库的某个表的更改信息，包括：46创建（Created）、47删除（Deleted）、93文件自动增长信息（Log File Auto Grow）、146修改（Alter）、20表示错误日志（Login Failed）

 

在生产环境中，以上几个分类都是比较常用的，对定位部分问题的定位能够在找到充分的证据可循，比如某厮将数据库数据删除掉了还不承认等，这里面的Login Failed信息，能够追踪出有那么用户尝试登陆过数据库，并且失败，如果大面积的出现这种情况，那就要谨防黑客袭击了。

 

当然，这里我还可以利用SQL Server自带的Profile工具，打开查看跟踪文件中的内容。

这个图像化的工具就比较熟悉了，直接打开进行筛选就可以了。

这种方式看似不错，但是它也有本身的缺点，我们来看：

1、这5个文件是滚动更新的，而且每个文件默认最大都为20MB，并且没有提供更改的接口，所以当文件填充完之后就会删除掉，所以会找不到太久以前的内容；