时间: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参数说明
[@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;查看数据库当前所有跟踪的信息
select * from sys.fn_trace_getinfo(0);设置敏感事件为SQL:BatchCompleted(12),收集信息:TextData(1), HostName(8), ClientProcessID(9), ApplicationName(10), LoginName(12)
declare @on bit;设置过滤条件:不收集应用程序名(AppName, 10)为'sqlcmd'的的信息
sp_trace_setfilter @trid, 10, 0, 1, N'SQLCMD'启动跟踪:
sp_trace_setstatus @trid, 2;查看当前过滤条件
select * from fn_trace_getfilterinfo(@trid);...(运行,收集数据)
查看跟踪文件:
select * from fn_trace_gettable(N'd:\sqltrace\mytrace.trc', default);...
停止跟踪
sp_trace_setstatus @trid, 0;关闭跟踪
sp_trace_setstatus @trid, 2;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;