当前位置:Gxlcms > mysql > T-SQL生成SQL追踪信息

T-SQL生成SQL追踪信息

时间:2021-07-01 10:21:17 帮助过:4人阅读

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,则SQLServer清除指定跟踪的所有过滤条件[@logical_operator=] logical_operator :int,无缺省值;指定条件应用的逻辑操作-与(AND,0), 或(OR, 1)[@comparison_operator=] comparison_operator :int,无缺省值;指定条件中比较操作类型:0: =(等于)1: <>(不等于)2: >(大于)3: <(小于)4: >=(大于等于)5:<=(小于等于)6: like7: not like[@value=] value :条件中的比较参考值

返回值: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;

人气教程排行