T-SQL生成SQL追踪信息

SQL Server中可以使用内置的T-SQL存程建立trace收集数据库访问信息。 1. 生成trace 使用 sp_trace_create 创建跟踪。新生成的trace为停止状态(未运行)。MSDN详细说明 sp_trace_create语法 sp_trace_create [ @traceid= ] trace_id OUTPUT , [ @options= ] op

SQL Server中可以使用内置的T-SQL存程建立trace收集数据库访问信息。

1. 生成trace

使用sp_trace_create创建跟踪。新生成的trace为停止状态(未运行)。MSDN详细说明

sp_trace_create语法

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=] trace_id ：int，新trace的ID。缺省值为NULL，用户输入值将被忽略，由系统指定。[@options=] option_value ：int，无缺省值。可指定为以下常量或组合。TRACE_FILE_ROLLOVER ：2；当trace文件到达最大值(由max_file_size)后，系统将关闭当前文件，并创建新的文件继续跟踪。新文件名称由用户指定文件名＋序号 构成。SHUTDOWN_ON_ERROR ：4；如果系统无法写入跟踪文件，关闭SQL Server。TRACE_PRODUCE_BLACKBOX ：8；指定服务器保留跟踪信息的最后5M内容；与其他选项不能同时使用。(详细说明见MSDN)[@tracefile=] 'trace_file' ：nvarchar(245)，无缺省值。指定跟踪信息所写入的文件。如：N'c:\sqltrace\mytrace.trc', 或N'\\servername\directory\mytrace.trc'。文件名中的后缀'trc'可忽略，由系统自动补全。如使用了 TRACE_FILE_ROLLOVER选项，建议在文件名中不要使用下划线'_'。[@maxfilesize=] max_file_size ：int,缺省为5，单位为M。指定单个trace文件的最大尺寸。如指定了TRACE_FILE_ROLLOVER选项，则系统将会在当前文件到达指定 大小后使用新的文件继续记录跟踪信息；如未指定ROLLOVER选项，系统将会在文件大小到达指定大小后停止记录。[@stoptime=] 'stop-time' ：datatime,缺省值为NULL。指定跟踪停止的时间。如为NULL，则手工控制或在数据库关闭时停止。[@filecount=] 'max_rollover_files' ：int,>=1。指定回滚文件数量上限。仅当使用了TRACE_FILE_ROLLOVER选项时有效。当回滚文件数量超过此上限后，系统将会在 创建新文件前删除最老的跟踪文件。

返回值：为0表示成功；其他值表示失败。

2. 获取跟踪信息

使用fn_trace_getinfo()可获取全部或指定trace信息。

语法：

sys.fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } )

参数：

trace_id：指定欲获取信息的跟踪的IDNULL,0, DEFAULT：获取全部trace信息

返回表

traceid ：跟踪的ID值Option ：选项信息1：trace options2：file name3：max file size4：stop time5：当前状态(0=stopped, 1=running)value：属性值3. 控制跟踪状态（启/停）

使用sp_trace_setstatus可控制跟踪启动/停止/关闭。

语法：

sp_trace_setstatus [ @traceid = ] trace_id, [ @status= ] status

参数：

[@traceid=] trace_id ：指定要控制的跟踪的ID[@status=] status ：指定跟踪状态：0:停止1:启动2:关闭指定跟踪并从数据库服务器中删除此跟踪。关闭前跟踪必须处于停止状态；跟踪所生成的跟踪信息文件不会被删除。

返回值：0：成功；其他值表示错误。

4. 增加/移除跟踪敏感事件

使用sp_trace_setevent控制跟踪敏感事件列表。MSDN详细说明

语法：

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

参数：

[@traceid=] trace_id ：指定要修改的目标跟踪ID[@eventid=] event_id ：指定要修改的事件。如：RPC:Completed(10)/SQL:BatchCompleted(12)/Audit Login(14)/SP:Starting(42)/Prepare SQL(71)/Cursor Excute(74)/Trace File Close(150)/...[@columnid=] column_id ：事件增/减的信息项(column)。如：TextData(1)/TransactionID94)/NTUserName(6) /HostName(8)/ClientProcessID(9)/ApplicationName(10)/LoginName(11)/DatabaseName(35)/RowCounts(48)/...[@on=] on ：bit, 事件(信息项)开关on=1, columnid=NULL：事件开，清除所有列on=1, columnid!=NULL：事件开，收集指定列信息on=0, columnid=NULL：事件关，清除所有列on=0, columnid!=NULL：不再收集指定事件的指定列信息

返回值：0：成功；其他值表示错误。

5. 过滤跟踪信息

sp_trace_setfilter用以设置跟踪过滤条件。MSDN详细说明

语法：

sp_trace_setfilter [ @traceid = ] trace_id , [ @columnid= ] column_id, [ @logical_operator= ] logical_operator, [ @comparison_operator= ] comparison_operator, [ @value= ] value

参数：

[@traceid=] trace_id ：指定跟踪ID[@columnid=] column_id ：int，无缺省值；指定过滤条件应用的列。如为NULL，则SQLＳerver清除指定跟踪的所有过滤条件[@logical_operator=] logical_operator ：int,无缺省值；指定条件应用的逻辑操作－与（AND，0）， 或(OR, 1)[@comparison_operator=] comparison_operator ：int,无缺省值；指定条件中比较操作类型：0: =(等于)1: (不等于)2: >(大于)3: =(大于等于)5: 返回值：0:成功；其他值表示错误 6. 其他跟踪相关存储过程

SQL Server还提供了一些其他跟踪相关存储过程，用以获取跟踪相关信息或对跟踪进行设置/自定义扩展。详细说明：MSDN:Introducing SQL Trace

sp_trace_generateevent 可结合触发器(Trigger)，或单独使用，触发自户定义事件（事件值82－91）。 详细说明fn_trace_geteventinfo：获取指定跟踪当前敏感事件表fn_trace_getfilterinfo：获取指定跟踪当前过滤条件表fn_trace_gettable：以表格形式获取指定跟踪文件中的内容...7. 一个简单例子

第一步，创建跟踪：设置跟踪文件大小上限为10M，回滚文件个数为10，记录文件为d:\sqltrace\mytrace.trc

declare @maxfilesize bigint;
declare @filecnt int;
set @trid = 0;
set @maxfilesize = 10;
set @filecnt = 10;
exec sp_trace_create @trid OUTPUT, 2, N'd:\sqltrace\mytrace', @maxfilesize, NULL, @filecnt;
select @trid;
go

查看数据库当前所有跟踪的信息

select * from sys.fn_trace_getinfo(0);
go

设置敏感事件为SQL:BatchCompleted(12)，收集信息：TextData(1), HostName(8), ClientProcessID(9), ApplicationName(10), LoginName(12)

declare @on bit;
set @on = 1;
exec sp_trace_setevent @trid,12,1, @on
exec sp_trace_setevent @trid,12,8, @on
exec sp_trace_setevent @trid,12,9, @on
exec sp_trace_setevent @trid,12,10, @on
exec sp_trace_setevent @trid,12,11, @on
go

设置过滤条件：不收集应用程序名(AppName, 10)为'sqlcmd'的的信息

sp_trace_setfilter @trid, 10, 0, 1, N'SQLCMD'
go

启动跟踪：

sp_trace_setstatus @trid, 2;
go

查看当前过滤条件

select * from fn_trace_getfilterinfo(@trid);
go

...(运行，收集数据)

查看跟踪文件：

select * from fn_trace_gettable(N'd:\sqltrace\mytrace.trc', default);
go

...

停止跟踪

sp_trace_setstatus @trid, 0;
go

关闭跟踪

sp_trace_setstatus @trid, 2;
go8. SQL Server2000的几点区别

SQL Server2000中，使用fn_trace系列系统存储过程时，需要在存储过程名前加"::"标识；

SQL Server2000中，仅当跟踪被停止(stop)并关闭(close)后，跟踪的内容才会写入文件中；

declare @maxfilesize bigint;
declare @filecnt int;
set @trid = 0;
set @maxfilesize = 10;
set @filecnt = 10;
exec sp_trace_create @trid OUTPUT, 2, N'd:\sqltrace\mytrace', @maxfilesize, NULL, @filecnt;
select @trid;
go

select * from sys.fn_trace_getinfo(0);
go

declare @on bit;
set @on = 1;
exec sp_trace_setevent @trid,12,1, @on
exec sp_trace_setevent @trid,12,8, @on
exec sp_trace_setevent @trid,12,9, @on
exec sp_trace_setevent @trid,12,10, @on
exec sp_trace_setevent @trid,12,11, @on
go

sp_trace_setfilter @trid, 10, 0, 1, N'SQLCMD'
go

sp_trace_setstatus @trid, 2;
go

select * from fn_trace_getfilterinfo(@trid);
go

select * from fn_trace_gettable(N'd:\sqltrace\mytrace.trc', default);
go

sp_trace_setstatus @trid, 0;
go

sp_trace_setstatus @trid, 2;