SQL Server 2008的日志跟踪利器- SQL trace

SQL trace实际上就是没有图形界面操作的SQL Server Profiler, 因为Profiler比较占用资源，不适合长时间的监听数据。那如何使用SQL trace,下面我就用图来说话，一步一步的带大家使用

1.打开SQL Server Profiler

2.在Profiler新建跟踪

 

3.定制筛选条件

例如：筛选sql语句

4.导出为脚本

5.打开导出后的脚本文件编辑

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%update%testtable%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - b760631b-50ab-41a7-b51a-688a6b6ca176'
-- 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

 @maxfilesize默认是5M，可以根据情况改大

 ‘InsertFileNameHere' 改成你要存放日志文件的路径名

6.执行该脚本，创建trace，执行成功会返回一个TraceId

  

7. 查看、停止、启动、删除trace

select * from sys.traces  查看当前有哪些traces,  status = 1表示正在运行的

status = 0 表示 停止

exec sp_trace_setstatus @traceId,0    --停止跟踪   改成1表示启动
exec sp_trace_setstatus @traceId,2    --删除跟踪

8. 查看跟踪的日志文件

我在win10下直接点击查看会提示拒绝访问，和文件权限有关，可以直接复制一份再打开即可

 

注：在SQL Server 2014高版本trace的日志在2008上打开报“找不到跟踪定义文件 Microsoft SQL Server TraceDefinition 12.0.0.xml”错误，如下图：

解决办法，在目标跟踪的SQL Server的TraceDefinitions目录下找到

Microsoft SQL Server TraceDefinition 12.0.0.xml文件，然后拷贝到本地的SQL Server

的TrarceDefinitions目录下

通常目录在C:\Program Files (x86)\Microsoft SQL Server\[内部版本]\Tools\Profiler\TraceDefinitions

[内部版本] SQL Server 2008 是100， 其他版本不一样，可以在目录下查找