/*
注1:最后的监控结果会存放在一个文件中(即下面的@filename)
有3种方法可以查看该文件
1、使用SQL命令直接查看
declare @filename nvarchar(256) = 'd:\coledata\trackdml\mytrace'
select * FROM fn_trace_gettable(@filename+'.trc', default);
2、将结果转化成数据表查看(tbname)
declare @filename nvarchar(256) = 'd:\coledata\trackdml\mytrace'
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO tbname
FROM fn_trace_gettable(@filename+'.trc', default);
3、在SQL SERVER PROFILER中打开该文件查看
注2:常见的trace操作
--1、查询当前有哪些trace在运行
select * from sys.fn_trace_getinfo(0)
/* 运行结果类似于:
traceid property value
1 1 2
1 2 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_178.trc
1 3 20
1 4 NULL
1 5 1
2 1 0
2 2 d:\coledata\trackdml\mytrace.trc
2 3 5
2 4 NULL
2 5 1
注1:每个跟跟踪在这里会显示5行记录,注意property=5的行,
如value=1则表示该trace正在运行,0表示已停止
注2:traceid=1的是SQLSERVER系统自己使用的,不要动。
*/
--2、启动或停止trace
exec sp_trace_setstatus @TraceID, @Status
--@TraceID: 即步骤1中查到的traceid, 这里是2
--@Status: 0表示停止该trace, 1表示启动, 2表示停止并删除该trace
*/
declare @dbname nvarchar(100) = 'DBNAME' --将DBNAME改成你要监控的数据库名
declare @filename nvarchar(256) = 'FILENAME' --将DBNAME改成你要存放监控结果的文件名,包括路径
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 64, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'INSERT %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'UPDATE %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'DELETE %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'TRUNCATE %'
exec sp_trace_setfilter @TraceID, 1, 0, 1, NULL
exec sp_trace_setfilter @TraceID, 35, 0, 6, @dbname
-- 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
SQL SERVER DML track
最新推荐文章于 2023-01-16 20:26:09 发布