Server中关于跟踪

Server中关于跟踪

前言

前言

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

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

简单的举几个应用场景:

简单的举几个应用场景:

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

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

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

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

 

 

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

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

 

 

一.查看系统默认跟踪信息(Default Trace)

一.查看系统默认跟踪信息(Default Trace)

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

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

select * from sys.configurations where configuration_id = 1568
select * from sys.configurations where configuration_id = 1568

永利皇宫登录网址 1

永利皇宫登录网址 1

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

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

select * from sys.traces
select * from sys.traces

永利皇宫登录网址 3

永利皇宫登录网址 3

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

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

永利皇宫登录网址 5

永利皇宫登录网址 6

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

永利皇宫登录网址 7

永利皇宫登录网址 8

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

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

select * from ::fn_trace_getinfo(0)
select * from ::fn_trace_getinfo(0)

永利皇宫登录网址 9

永利皇宫登录网址 9

以上命令返回的结果值,各个值(property)代表的含义如下:

以上命令返回的结果值,各个值(property)代表的含义如下:

第一个:2表示滚动文件;

第一个:2表示滚动文件;

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

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

第三个:表示滚动文件的大小(单位MB),当到达这个值就会创建新的滚动文件

第三个:表示滚动文件的大小(单位MB),当到达这个值就会创建新的滚动文件

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

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

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

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

 

 

找到该目录,我们查看下该文件:

找到该目录,我们查看下该文件:

永利皇宫登录网址 11

永利皇宫登录网址 11

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

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

 

 

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

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

永利皇宫登录网址 13

永利皇宫登录网址 13

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

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

永利皇宫登录网址 15

永利皇宫登录网址 16

--获取跟踪文件中前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('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', 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] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' 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
--获取跟踪文件中前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('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', 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] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' 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

永利皇宫登录网址 17

永利皇宫登录网址 18

永利皇宫登录网址 19

永利皇宫登录网址 19

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

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

 

 

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

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

 

 

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

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

永利皇宫登录网址 21

永利皇宫登录网址 21

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

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

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

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

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

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

2、本身默认的跟踪,只是提供一些关键信息的追踪,其中包括:auditing
events,database events,error events,full text events,object
creation,object deletion,object
alteration,想要找到其它更详细的内容,此方式可能无能为力;

2、本身默认的跟踪,只是提供一些关键信息的追踪,其中包括:auditing
events,database events,error events,full text events,object
creation,object deletion,object
alteration,想要找到其它更详细的内容,此方式可能无能为力;

3、在SQL Server2012后续版本的 Microsoft SQL Server
将删除该功能,改用扩展事件。

3、在SQL Server2012后续版本的 Microsoft SQL Server
将删除该功能,改用扩展事件。

 

 

二.自定义跟踪信息(Default Trace)

二.自定义跟踪信息(Default Trace)

根据上面SQL Server自带的跟踪信息有一些局限性,SQL
Server为我们提供了自定义跟踪的接口,我们可以自己定义跟踪,充分扩展方法。

根据上面SQL Server自带的跟踪信息有一些局限性,SQL
Server为我们提供了自定义跟踪的接口,我们可以自己定义跟踪,充分扩展方法。

利用如下系统存储过程,我们可以创建自定义的Trace

利用如下系统存储过程,我们可以创建自定义的Trace

sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value  
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]
sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value  
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]

@traceid  系统默认分配跟踪的ID号

@traceid  系统默认分配跟踪的ID号

@options 指定为跟踪设置的选项,系统默认提供的几个选项:

*永利皇宫登录网址 ,*@options **指定为跟踪设置的选项,系统默认提供的几个选项:

                  2表示当文件写满的时候,关闭当前跟踪并创建新文件。

                  2表示当文件写满的时候,关闭当前跟踪并创建新文件。

                  4表示如果不能将跟踪写入文件,不管什么原因导致,SQL
Server则会关闭。这个可以利用此选项,追踪问题

                  4表示如果不能将跟踪写入文件,不管什么原因导致,SQL
Server则会关闭。这个可以利用此选项,追踪问题

                  8制定服务器产生的最后5MB的跟踪信息记录由服务器保存。

                  8制定服务器产生的最后5MB的跟踪信息记录由服务器保存。

@tracefile 跟踪文件的路径,这里可以是share的路径

@tracefile 跟踪文件的路径,这里可以是share的路径

@maxfilesize 跟踪文件的大小,单位是MB,默认不设置为5MB

@maxfilesize 跟踪文件的大小,单位是MB,默认不设置为5MB

@stoptime 跟踪停止的时间,利用它我们可以定时跟踪结束的日期

@stoptime 跟踪停止的时间,利用它我们可以定时跟踪结束的日期

@filecount 默认生产的跟踪文件的数量,比如默认的为5个,那就在第5个文件写完的时候进行覆盖第1个文件滚动

@filecount 默认生产的跟踪文件的数量,比如默认的为5个,那就在第5个文件写完的时候进行覆盖第1个文件滚动

 

 

比如我们可以利用如下脚本进行创建 

比如我们可以利用如下脚本进行创建 

永利皇宫登录网址 23

永利皇宫登录网址 24

--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'F:\SQLTest\'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=5
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5

exec @rc=sp_trace_Create
@TraceID output,
@options,
@TraceFilePath,
@maxfilesize,
@EndTime,
@filecount
if(@rc=0)
select  @TraceID
--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'F:\SQLTest\'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=5
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5

exec @rc=sp_trace_Create
@TraceID output,
@options,
@TraceFilePath,
@maxfilesize,
@EndTime,
@filecount
if(@rc=0)
select  @TraceID

永利皇宫登录网址 25

永利皇宫登录网址 26

我们通过上面的跟踪创建的过程,可以在系统自带的默认的sys.traces中找到该跟踪的明细
永利皇宫登录网址 27

我们通过上面的跟踪创建的过程,可以在系统自带的默认的sys.traces中找到该跟踪的明细
永利皇宫登录网址 27

select * from sys.traces
where id=2
select * from sys.traces
where id=2

永利皇宫登录网址 29

永利皇宫登录网址 29

通过上面的脚本,我们已经创建了一个新的跟踪(trace),但是这个跟踪状态为0,也就是说还没有运行,下面我们的步骤就是要为这个跟踪添加事件(event)

通过上面的脚本,我们已经创建了一个新的跟踪(trace),但是这个跟踪状态为0,也就是说还没有运行,下面我们的步骤就是要为这个跟踪添加事件(event)

 

 

这个也是利用SQL Server为我们提供的操作函数

这个也是利用SQL Server为我们提供的操作函数

sp_trace_setevent [ @traceid = ] trace_id  
          , [ @eventid = ] event_id 
          , [ @columnid = ] column_id 
          , [ @on = ] on
sp_trace_setevent [ @traceid = ] trace_id  
          , [ @eventid = ] event_id 
          , [ @columnid = ] column_id 
          , [ @on = ] on

@traceid 要修改的跟踪的 ID号

@traceid 要修改的跟踪的 ID号

@eventid 要打开的事件的 ID

@eventid 要打开的事件的 ID

网站地图xml地图