澳门游艺场9159-9159金沙游戏场

(Trace)走进数据库背后,如果我们利用好了跟踪技

作者: 澳门游艺场  发布:2019-10-04

前言

前言

SQL追踪(SQL Trace)是一个轻量级的追踪工具,按照事件(Events)记录数据库发生的消息,几乎对数据库性能没有什么影响。SQL Server内置一个Trace,称作默认追踪(Default Trace),默认追踪的ID是1,大家经常使用的SQL Server Profiler,就是利用SQL Trace记录数据库活动的一个工具。SQL Trace在SQL Server数据库引擎种出现的比较早,可以被性能和功能更卓越的扩展事件(Extended Events)取代。

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

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

一,查看默认追踪是否启用

简单的举几个应用场景:

简单的举几个应用场景:

默认追踪是系统内置的,TraceID是1,默认是开启的,可以通过系统配置表 sys.configurations 进行查看,配置项ID(configuration_id)是1568:

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

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

图片 1

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

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

字段 value=1,表示Default Trace是开启的。

 

 

二,禁用或启用默认追踪

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

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

如果默认追踪被禁用,需要重新配置启用默认追踪:

 

 

exec sp_configure 'show advanced options' , 1 ;
go
reconfigure;
go

exec sp_configure 'default trace enabled' , 1 ;
go
reconfigure;
go

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

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

如果默认追踪已经启用,可以重新配置禁用默认追踪:

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

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

exec sp_configure 'default trace enabled' , 0 ;
go
reconfigure;
go

exec sp_configure 'show advanced options' , 0 ;
go
reconfigure;
go
select * from sys.configurations where configuration_id = 1568
select * from sys.configurations where configuration_id = 1568

三,查看默认追踪的信息

图片 2

图片 3

默认追踪记录的数据存储在文件中,可以从系统视图 sys.traces查看文件的路径,文件的大小(Size)和文件的更新方式等信息,追踪文件默认的扩展名是 .trc。

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

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

select id
    ,iif(status=1,'running','stopped') as status
    ,path
    ,max_size
    ,start_time
    ,stop_time
    ,event_count
    ,max_files
    ,is_rowset
    ,is_rollover
    ,is_shutdown
    ,is_default
    ,buffer_count
    ,buffer_size as each_buffer_size
from sys.traces
where id=1
select * from sys.traces
select * from sys.traces

默认追踪有5个跟踪文件,每一个文件的最大size默认是20MB,SQL Server负责维护这5个文件,当实例重启的时候或者到达文件Size最大值的时候,SQL Server创建新的文件,将最早创建的跟踪文件删除,依次滚动(Rollover)更新。

图片 4

图片 5

四,查看追踪文件的内容

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

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

函数sys.fn_trace_gettable,用于从追踪文件中读取数据,以关系表的格式显式:

图片 6

图片 7

sys.fn_trace_gettable ( 'filename' , number_files )
--开启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

参数filename:用于指定追踪文件的名称,其值可以从系统视图sys.traces 中的path获取;

图片 8

图片 9

参数number_files:如果number_files 被指定为default,函数读取所有的滚动文件。

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

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

函数返回的是关系表,有效字段是:追踪关联的事件绑定的字段,

select * from ::fn_trace_getinfo(0)
select * from ::fn_trace_getinfo(0)
select * 
from sys.fn_trace_gettable(N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLoglog_4.trc',default)

图片 10

图片 11

五,查看默认追踪记录的事件列表

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

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

函数fn_trace_geteventinfo(trace_id)返回追踪关联的事件列表,使用该函数可以查看默认追踪记录的事件和事件的特定字段:

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

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

select categ.name as category,
    te.trace_event_id as event_id, 
    te.name as event_name,
    tc.trace_column_id as event_column_id,
    tc.name as column_name,
    tc.type_name as column_type
from sys.fn_trace_geteventinfo(1) as gei
inner join sys.trace_columns tc 
    on gei.columnid=tc.trace_column_id
inner join sys.trace_events te 
    on gei.eventid=te.trace_event_id
inner join sys.trace_categories categ
    on te.category_id=categ.category_id
order by category,event_id,event_column_id

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

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

六,查看事件和Category

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

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

Category用于组织事件(Event),是事件的分组,在SQL Server 2012中,共有21个Category,180个Event,每个Event属于唯一的一个Category。

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

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

select tc.name as category,
    te.trace_event_id as event_id,
    te.name as event_name
from sys.trace_categories tc 
inner join sys.trace_events te 
    on tc.category_id=te.category_id
order by category,event_id

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

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

七,查看事件绑定的字段

 

 

在SQL Server 2012中,事件共有66个字段,但不是每个Event都能绑定所有的66个字段,每个Event能够绑定的字段是固定的,系统预先设置,用户不能修改,视图 sys.trace_event_bindings 用于显示每个事件绑定的字段。

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

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

select te.trace_event_id as event_id, 
    te.name as event_name,
    tc.trace_column_id as column_id,
    tc.name as column_name,
    tc.type_name as column_type
from sys.trace_event_bindings teb 
inner join sys.trace_columns tc 
    on teb.trace_column_id=tc.trace_column_id
inner join sys.trace_events te 
    on teb.trace_event_id=te.trace_event_id
order by event_id,column_id

图片 12

图片 13

八,使用SQL Server Profiler创建SQL Trace

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

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

如果用户需要创建自定义的追踪,那么可以使用系统提供的存储过程来实现,但是,使用TSQL代码创建追踪的过程十分繁琐,代码量庞大,整个过程不直观。大家知道,SQL Server Profiler是一个可视化用于查看数据库活动的工具,同时,它也是一个用于创建SQL Trace的工具。使用SQL Server Profiler创建SQL Trace的过程十分简单:选择相应的事件和事件的字段之后,导出SQL Trace  的定义即可。

 

 

在创建SQL Trace之后,点击File->Export->Scipt Trace Definition,把SQL Server Profiler用于创建SQL Trace的脚本代码导出:

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

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

图片 14

图片 15

图片 16

导出的脚本如下,不能直接使用,必须修改一处代码:在创建Trace时,指定存储追踪数据的文件(File) 或 关系表(Table),仅此而已。

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

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

图片 17图片 18

图片 19

图片 20

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Client side File and Table cannot be scripted
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on

--delete many commands here ---

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%drop%'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go
--获取跟踪文件中前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:dataDefaultFileMangerMSSQL10.MSSQLSERVERMSSQLLoglog_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:dataDefaultFileMangerMSSQL10.MSSQLSERVERMSSQLLoglog_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

View Code

图片 21

图片 22

 

图片 23

图片 24

注:SQL Trace是被扩展事件取代的功能,在后续的版本中将会被移除,建议在以后的开发中使用扩展事件。

 我创建了一张表,通过上面的跟踪,可以跟踪到该记录的信息,根据不同的过滤信息,我们可以查询出到跟踪的某个库的某个表的更改信息,包括: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 Trace

 

 

Server-wide Configuration Catalog Views (Transact-SQL).aspx)

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

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

System Trace Functions.aspx)

图片 25

图片 26

SQL Server 默认跟踪(Default Trace)

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

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

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

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

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

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)

本文由澳门游艺场9159发布于澳门游艺场,转载请注明出处:(Trace)走进数据库背后,如果我们利用好了跟踪技

关键词: