代码改变世界

SQL Server 默认跟踪(Default Trace)

2013-10-09 12:54  听风吹雨  阅读(18345)  评论(11编辑  收藏

一.本文所涉及的内容(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;

wps_clip_image-9845

(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)

wps_clip_image-29635

(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

wps_clip_image-22331

(Figure3:Create事件前半部分信息)

wps_clip_image-4443

(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

wps_clip_image-8525

(Figure5:Alter事件前半部分信息)

wps_clip_image-12980

(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

wps_clip_image-1593

(Figure7:Drop事件后半部分信息)

五.补充说明(Addon)

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

wps_clip_image-21031

(Figure8:log*.trc文件)

wps_clip_image-16855

(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%'

wps_clip_image-17291

(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

wps_clip_image-10897

(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)