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, 其他版本不一样,可以在目录下查找