>

SQL Server中追踪器Trace的介绍和轻巧利用

- 编辑:乐百家599手机首页 -

SQL Server中追踪器Trace的介绍和轻巧利用

--设置跟踪状态以启动
exec sp_trace_setstatus @TraceID,1

里面最关键的正是光阴ID,那几个是SQL Server为大家提供的一些列的码表时间值,具体值能够参照联机丛书 sp_trace_setevent (Transact-SQL).aspx)

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

一.What is Trace?

对于SQL Profiler这么些工具相信我们都不是很生分,没用过的对象能够在SQL Server Management Studio>工具>SQL Server Profiler处使用。这些工具是用来监督SQL,存款和储蓄进程的实践,客商登入等等音信。但那几个工具只是三个GUI,他的真面目正是Trace。上边是Trace的架构:

乐百家lo599 1  

数据库引擎会发生一多种事件,然后依次trace能够去订阅本身感兴趣的事件,一旦数据库发生了有关事件就能够发放订阅该事件的trace,各样trace通过谐和的过滤器对该事件的新闻过滤(举例:捕获实行时间抢先1秒的SQL语句),然后嵌入七个缓冲队列里,最终写入文件只怕是部分客户端应用。

简简单单介绍完理论,下边就从头执行:

select * from sys.traces

 

Category用于集体育赛事件(伊夫nt),是事件的分组,在SQL Server 2011中,共有十多个Category,1柒十几个Event,每一个Event属于独一的二个Category。

How to Create a Trace?

要创设叁个追踪器,总共分三步:

1.实行存储进度sp_trace_create创设二个追踪器

2.施行存款和储蓄进程sp_trace_setevent增多本身想订阅的平地风波以及最后结出集的列名

3.奉行存款和储蓄进度sp_trace_setfilter设置过滤器来对过滤产生多少

下边是八个创办一个追踪器的SQL脚本

 1 DECLARE @return_code INT;
 2 DECLARE @TraceID INT;
 3 DECLARE @maxfilesize BIGINT;
 4 SET @maxfilesize = 5;
 5 --step 1: create a new empty trace definition
 6 EXEC sp_trace_create
 7                 @traceid OUTPUT
 8                , @options = 2
 9                , @tracefile = N'C:TraceFilesLongRunningQueries'
10                , @maxfilesize = @maxfilesize
11     , @stoptime =NULL
12     , @filecount = 2; 
13 -- step 2: add the events and columns
14 EXEC sp_trace_setevent
15                 @traceid = @TraceID
16                , @eventid = 10 -- RPC:Completed
17                , @columnid = 1 -- TextData
18                , @on = 1;--include this column in trace
19 EXEC sp_trace_setevent
20                 @traceid = @TraceID
21                , @eventid = 10 -- RPC:Completed
22                , @columnid = 13 --Duration
23                , @on = 1;--include this column in trace
24 EXEC sp_trace_setevent
25                 @traceid = @TraceID
26                , @eventid = 10 -- RPC:Completed
27                , @columnid = 15 --EndTime
28                , @on = 1;--include this column in trace  
29 EXEC sp_trace_setevent
30                 @traceid = @TraceID
31                , @eventid = 12 -- SQL:BatchCompleted
32                , @columnid = 1 -- TextData
33                , @on = 1;--include this column in trace
34 EXEC sp_trace_setevent
35                 @traceid = @TraceID
36                , @eventid = 12 -- SQL:BatchCompleted
37                , @columnid = 13 --Duration
38                , @on = 1;--include this column in trace
39 EXEC sp_trace_setevent
40                 @traceid = @TraceID
41                , @eventid = 12 -- SQL:BatchCompleted
42                , @columnid = 15 --EndTime
43                , @on = 1;--include this column in trace        
44 -- step 3: add duration filter
45 DECLARE @DurationFilter BIGINT;
46 SET @DurationFilter = 10000000; --duration in microseconds
47 EXEC sp_trace_setfilter
48                 @traceid = @TraceID
49                , @columnid = 13
50                , @logical_operator = 0 --AND
51                , @comparison_operator = 4 -- greater than or equal to
52                , @value = @DurationFilter; --filter value
53 SELECT @TraceID AS TraceID;

对于第九行中的C:TraceFilesLongRunningQueries,请确定保证TraceFiles文件夹存在,而LongRunningQueries是文件名,成立后会自动加上.trc后缀。

对此过滤器中现实的风云以及列名,大家可以参照:

对此像本人一样的一对初学者,写上边的本子也许相比棘手,那么大家可以通过SQL Server Profiler配置各个须要,然后导出脚本:

乐百家lo599 2

接下来把门路等一名目好多其余参数设置下就可以。

乐百家lo599 3

理之当然,这里小编还足以使用SQL Server自带的Profile工具,展开查看追踪文件中的内容。

SQL Server 暗中同意追踪(Default Trace)

四.How to Viewing Trace Data?

近期早就说过,追踪器最后把追踪到的消息写到了一个文本里,也正是大家创制时内定的路线。通过三个连串函数就能够查阅深入分析这个多少了:

SELECT *
FROM fn_trace_gettable(N'C:TraceFilesLongRunningQueries.trc',DEFAULT);

乐百家lo599 4

 

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

五.Summary

SQL Trace对象提供劳务器端的寻踪的技巧,包括部分仓库储存进度,数据库,视图来成立trace, 通过选拔T-SQL和trace揭发的元数据来治本trace数据,这个都以SQL Profiler所不能够提供的

 

我们定义追踪全部语句批量操作的寻踪,从地点表大家得以寻找到为12,13

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

乐百家lo599 5乐百家lo599 6

三.How to Operate a Trace?

地点我们曾经把创设了一个跟踪器,但那么些追踪器方今尚未开端运转,大家得以因此上边包车型大巴脚本来查看trace的状态

select * from sys.traces

实践之后你会发觉有2个trace记录,第四个是SQL Server私下认可的trace,它提供极致简单的意义,第三个就是大家刚刚创设的trace

乐百家lo599 7

status正是指追踪器的景况

@status Action
0 Stops the trace
1 Starts the trace
2 Closes the trace and deletes its definition

 

 

 

操作status的脚本:

-- stop  the trace
 DECLARE @TraceID int ;
 SET @TraceID = 2 ; -- specify value from sp_trace_create
 EXEC sp_trace_setstatus
    @traceid = @TraceID
    ,@status = 0 ;-- stop trace
 -- delete the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 2 ;-- delete trace
 -- start the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 1 ;-- start trace

咱俩由此上边的跟踪创制的进程,能够在系统自带的暗中认可的sys.traces中找到该追踪的留心
乐百家lo599 8

SQL Server 二零零六 - Default Trace (暗许追踪)

函数fn_trace_geteventinfo(trace_id)再次来到追踪关联的事件列表,使用该函数能够查阅暗中认可追踪记录的平地风波和事件的一定字段:

由此如下脚本删除到具有的追踪

乐百家lo599 9

参照文书档案:

这么些中最常用的正是:

乐百家lo599 10

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

乐百家lo599 11

                  8制定服务器产生的末段5MB的追踪消息记录由服务器保存。

乐百家lo599 12

乐百家lo599 13

**@columnid **要为该事件增加的列的 ID

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

@traceid 要修改的追踪的 ID号

乐百家数据库,SQL Server 默许追踪(Default Trace)

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

我们得以利用如下语句,查找追踪文件的信息

小编们精晓在SQL Server默许的追踪文件在实例重启时候,都会收敛,所以大家能够通过如下方法消除,保险在历次实例重新开动的时候都会推行该追踪

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

经过上述的布置会自行配置成七个滚动文件,当达到暗中同意的最大文件容积5MB的时候,就在三个文本中循环滚动依次更新。

当然如若认为生成的文件5MB有一点小,能够手动配置改变大小,可能自定义文件路线,那么些都以允许自定义设置的。

三,查看暗许追踪的信息

select * from sys.configurations where configuration_id = 1568

乐百家lo599 14

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

首先个:2意味滚动文件;

乐百家lo599,在生产条件中,以上多少个分类都以比较常用的,对定点部分主题材料的定点能够在找到充足的凭证可循,举个例子某厮将数据库数据删除掉了还不认账等,那之中的Login Failed新闻,能够追踪出有那么客户尝试登入过数据库,何况失利,若是普及的产出这种状态,那将要防范黑客袭击了。

四,查看追踪文件的内容

 

小编们可以利用如下语句,查找追踪文件的音信

导出的脚本如下,不能一向利用,必须修改一处代码:在开立Trace时,钦赐期存款款和储蓄追踪数据的文书(File) 或 关系表(Table),仅此而已。

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

大家经过以下命令来查看追踪文件中的内容:

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

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

乐百家lo599 15

 

SQL 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' ]

乐百家lo599 16

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

经过如下存储进程,将大家自定的寻踪运转

系统暗中同意提供5个追踪文件,而且每叁个文件暗许大小都是20MB,SQL Server会自个儿维护那5个文本,当实例重启的时候依旧达到最大值的时候,之后会另行生成新的文本,将最先的跟踪文件删除,依次滚动更新。

一,查看暗中认可追踪是还是不是启用

依靠地点SQL Server自带的追踪新闻有一点局限性,SQL Server为大家提供了自定义跟踪的接口,大家得以自个儿定义追踪,丰硕扩张方法。

二.自定义跟踪新闻(Default Trace)

SQL追踪(SQL Trace)是八个轻量级的寻踪工具,依照事件(Events)记录数据库发生的音讯,大致对数据库品质未有怎么震慑。SQL Server内置二个Trace,称作暗中认可追踪(Default Trace),默许追踪的ID是1,我们平常应用的SQL Server Profiler,正是选用SQL Trace记录数据库活动的三个工具。SQL Trace在SQL Server数据库引擎种出现的可比早,能够被质量和功用更卓绝的扩充事件(Extended 伊夫nts)取代。

DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize=25
DECLARE @tracefile nvarchar(245)
SET @tracefile=N'F:SQLTestTRACEMYTRACE.trc'
EXEC sp_trace_create
@TraceID OUTPUT,
@options=8,
@tracefile=NULL,
@maxfilesize=@maxfilesize
EXEC sp_trace_setstatus @TraceID,1

方法参照他事他说加以考察本篇的上半有的。

在SQL Server 2013中,事件共有陆十五个字段,但不是种种伊芙nt都能绑定全体的陆十多个字段,各类Event能够绑定的字段是稳固的,系统预先安装,顾客不能够修改,视图 sys.trace_event_bindings 用于体现每一种事件绑定的字段。

乐百家lo599 17

乐百家lo599 18

二,禁止使用或启用暗许追踪

@filecount 暗中认可生产的追踪文件的多少,例如默许的为5个,这就在第5个文本写完的时候举行覆盖第三个公文滚动

--新建追踪的存储过程
use master
go
create proc StartBlackBoxTrace
as
begin
    --默认开启追踪所有的SQL 执行语句,文件文件路径为默认
    DECLARE @TraceID int
    DECLARE @MaxFileSize bigint
    SET @MaxFileSize=25
    EXEC SP_TRACE_CREATE
    @TraceID OUTPUT,
    8,
    NULL,
    @MaxFileSize
    EXEC SP_TRACE_SETSTATUS @TraceID,1
END

--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
GO

万一默许追踪已经启用,能够重新配置禁用暗许追踪:

                  4代表借使无法将追踪写入文件,不管什么样原因变成,SQL Server则会关闭。那几个能够利用此选项,追踪难题

乐百家lo599 19

函数重回的是关乎表,有效字段是:跟踪关联的风浪绑定的字段,

乐百家lo599 20

那些中最常用的正是:

SQL Server中有关追踪(Trace)那一点事

经过如下命令实行追踪的关门

结语

 

 

--设置跟踪状态以启动
exec sp_trace_setstatus @TraceID,1

默许追踪有5个追踪文件,每一个文本的最大size私下认可是20MB,SQL Server负担维护那5个文件,当实例重启的时候依然达到文件Size最大值的时候,SQL Server创立新的文本,将最先创建的追踪文件删除,依次滚动(Rollover)更新。

这种办法临近理所当然,不过它也是有本身的瑕玷,大家来看:

 

暗中同意追踪记录的多少存款和储蓄在文书中,能够从系统视图 sys.traces查看文件的门径,文件的深浅(Size)和文书的换代格局等消息,追踪文件默许的强大名是 .trc。

粗略的举几个应用场景:

经过如下存款和储蓄进程,将大家自定的追踪运营

乐百家lo599 21

接下来设置,暗中同意的SPID为超越等于50,小于50的为系统自有事件

exec sp_trace_setevent 2,12,1,1
exec sp_trace_setevent 2,13,1,1
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

将咱们刚刚的富有操作,已经追踪出来了。

那个也是采纳SQL Server为大家提供的操作函数

若是客商须要创立自定义的寻踪,那么能够运用系统提供的仓库储存进程来完结,可是,使用TSQL代码创造追踪的进程非常麻烦,代码量强大,整个经过不直观。我们精通,SQL Server Profiler是叁个可视化用于查看数据库活动的工具,同期,它也是三个用于制造SQL Trace的工具。使用SQL Server Profiler成立SQL Trace的进度极度轻松:选拔相应的平地风波和事件的字段之后,导出SQL Trace  的定义就可以。

乐百家lo599 22

将该脚本保存到三个地方,然后我们开发,笔者顺便将默许的公文路线加多上

私下认可追踪是系统内置的,TraceID是1,暗中同意是张开的,能够通过系统计划表 sys.configurations 举办查看,配置项ID(configuration_id)是1568:

--创建跟踪文件返回值
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

3、在SQL Server2012后续版本的 Microsoft SQL Server 将去除该意义,改用扩充事件。

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

上述代码参照院子里大拿宋沄剑,这里大家选取系统的自带的profile进行统一筹算追踪,大家一分区直属机关接公投择系统自带的死锁模板,举行追踪

黑匣子作为飞机出现事故后的追踪利器,在微软的SQL Server那些数据库中暗中认可也给装上了此引擎,可是并未有拉开,此意义大概扶持我们会诊间歇性的服务器崩溃。它比大家地方介绍的私下认可开启的跟踪盯梢的音讯更全,追踪更加大学一年级部分。其内容包括了:“SP:运维”、“SQL:批管理运营”、极度和注意等事件

注:SQL Trace是被扩充事件代表的服从,在继续的版本司令员会被移除,提出在现在的开拓中应用扩充事件。

将该脚本保存到叁个地方,然后大家展开,小编顺便将私下认可的公文路线增多上

乐百家lo599 23

 

 

                  4代表一旦不可能将跟踪写入文件,不管什么原因形成,SQL Server则会关闭。那一个可以行使此选项,追踪难点

System Trace Functions.aspx)

@traceid  系统暗中同意分配追踪的ID号

乐百家lo599 24

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

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

黑匣子作为飞机出现事故后的追踪利器,在微软的SQL Server那个数据库中暗中同意也给装上了此引擎,但是并未有开启,此成效恐怕帮助我们检查判断间歇性的服务器崩溃。它比大家地点介绍的暗中认可开启的追踪盯梢的新闻更全,跟踪更加大一些。其剧情蕴含了:“SP:运维”、“SQL:批管理运行”、极度和留心等事件

上述命令归来的结果值,各样值(property)代表的意义如下:

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

乐百家lo599 25

@traceid  系统暗中认可分配追踪的ID号

八,使用SQL Server Profiler创建SQL Trace

本文由乐百家数据库发布,转载请注明来源:SQL Server中追踪器Trace的介绍和轻巧利用