SQL 探查器跟踪使用系统存储过程来建立你可以从个现有探查器跟踪中使用SQL命令并构造你自己存储过程来创建和启动个SQL探查器跟踪你需要使用自己存储过程来指定些额外设置这些设置包括运行时间、文件大小和跟踪输出文件位置下面列出了有关如何使用这个存储过程来创建、执行并且关闭探查器跟踪详细过程

　　创建跟踪定义

　　定义用于构造个探查器跟踪SQL命令最有效思路方法是使用SQL探查器

　　1. 启动SQL探查器并选择File > New Trace指定你在跟踪中想要事件、字段和过滤器

　　2. 启动跟踪然后停止它

　　3. 输出定义点击File > Export > Script Trace Definition > For SQL Server 2005

　　注意对于SQL Sever 2000 和 2008请选择适当输出类型

　　4. 保存跟踪文件

　　创建个探查器跟踪存储过程

　　接着选择这些输出跟踪定义并且用它来创建个存储过程

　　1. 使用SSMS来打开上面创建输出跟踪定义

　　2. 在SSMS中打开另个查询窗口并粘贴下面trc_template存储过程代码

　　

/*
　　use Admin

　　go

　　*/

　　CREATE procedure trc_Template @Folder nvarchar(200)

　　as

　　/*

　　Start a 60 minute profiler trace storing the captured output in

　　provider folder.

　　The folder must exist. A subfolder will be created using the start date

　　and time to allow for repeated running of this profile without

　　replacing the

　　previuos captured trace files.

　　On SQL Server 2005, XP_CMDSHELL needs to be enable to create the

　　subfolder. You

　　might want to disable it when you are done running your scheduled

　　trace.

　　Sample Command: exec trc_Template @Folder =

　　'C:\Output\ProfilerTrace\Template'

　　*/

　　 nocount _disibledevent=>
　　declare @StartDatetime varchar(13) ; @StartDatetime =

　　convert(char(8),getdate,112) + '_' +

　　cast(replace(convert(varchar(5),getdate,108),':','') as char(4)) --['YYYYMMDD_HHMM']

　　declare @rc

　　declare @TraceID

　　declare @TraceFile nvarchar(100)

　　declare @MaxFileSize big ; @MaxFileSize = 50 -- The maximum trace file in megas

　　declare @cmd nvarchar(2000)

　　declare @msg nvarchar(200)

　　If right(@Folder,1)<>'\' @Folder = @Folder + '\'

　　-- Check Folder exists

　　 @cmd = 'dir ' +@Folder

　　exec @rc = master..xp_cmdshell @cmd,no_output

　　 (@rc != 0) begin @msg = 'The specied folder ' + @Folder + '

　　does not exist, Please specy an existing drive:\folder '+ cast(@rc as

　　varchar(10)) raiserror(@msg,10,1) (-1)

　　end

　　--Create trace file folder

　　 @cmd = 'mkdir ' +@Folder+@StartDatetime

　　exec @rc = master..xp_cmdshell @cmd,no_output

　　 (@rc != 0) begin @msg = 'Error creating trace folder : ' +

　　cast(@rc as varchar(10)) @msg = @msg + 'SQL Server 2005 or later

　　instance require OLE Automation to been enabled' raiserror(@msg,10,1)

　　(-1)

　　end

　　 @TraceFile = @Folder+@StartDatetime+'\trace'

　　exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,

　　@MaxFileSize, @StopTime

　　 (@rc != 0) begin @msg = 'Error creating trace : ' + cast(@rc as

　　varchar(10)) raiserror(@msg,10,1) (-1)

　　end

　　--> Using your saved trace file, add the '-- Set the events' section below <--

　　--> Using your saved trace file, add the '-- Set the Filters' section below <--

　　--> Customization is now completed <--

　　-----------------------------------------------------------------------------

　　-- This filter is added to exclude all profiler traces.

　　exec sp_trace_filter @TraceID, 10, 0, 7, N'SQL Profiler%'

　　-- Set the trace status to start

　　exec sp_trace_status @TraceID, 1 -- start trace

　　select 'Trace id = ', @TraceID, 'Path=', @Folder+@StartDatetime+'\'

　　select 'To Stop this trace sooner, execute these two commands'

　　select ' EXEC sp_trace_status @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'

　　select ' EXEC sp_trace_status @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'

　　

　　go


　　3. 在输出跟踪定义中找到'-- Set the events'部分复制所有代码行直到下个注释行为'-- Set the Filters'



　　4. 把这些代码行粘贴到代码行"add the '-- Set the events' section below"后面存储过程模板

　　

　　图

　　5. 接着找到输出跟踪定义'-- Set the Filters'部分粘贴'-- Set the trace status to start'的前代码行

　　6. 把这些代码行粘贴到"add the '-- Set the Filters' section below"后面存储过程模版

　　

　　图 2

　　7. 使用你命名规则更改存储过程名

　　8. 在SQL代码中探查器终止时间通过变量@StopTime来设置在跟踪启动后当前设置是60分钟你可以调整这个设置成适合你具体情况

　　9. 把@MaxFileSize设置成适合跟踪输出文件大小目前它被设置成50兆字节

　　10. 把存储过程代码保存在个非trc__Template.sql新文件名下而

　　准备服务器

　　1. 在将要用于包含探查器跟踪文件服务器下创建个文件夹

　　2. 在SQL Server 2005和 2008中启用XP_CMDSHELL如果你不想直启用这个功能那么你可以限制启动命令命令该命令启用XP_CMDSHELL启动这个跟踪然后停用XP_CMDSHELL(看下面举例代码)

　　3. 使用你上面创建文件在SQL Server例子上创建存储过程它可以在任何用户数据库中创建在下面例子中它在数据库Admin中创建

　　4. 在SQL代理中创建个作业来执行这个存储过程不要启用该作业除非你已经测试了这个存储过程

　　 -- Enable xp_cmdshell
　　EXEC sp_configure 'show advanced options', 1

　　-- To update the currently configured value for advanced options.

　　RECONFIGURE

　　-- To enable the feature.

　　EXEC sp_configure 'xp_cmdshell', 1

　　-- To update the currently configured value for this feature.

　　RECONFIGURE

　　-- Start profiler trace

　　EXEC Admin.dbo.trc_PerformanceTuning @Folder = 'e:\Output\ProfilerTrace\PerformanceTuning'

　　-- Disable xp_cmdshell

　　EXEC sp_configure 'xp_cmdshell', 0

　　-- To update the currently configured value for this feature.

　　RECONFIGURE

　　EXEC sp_configure 'show advanced options', 0

　　-- To update the currently configured value for advanced options.

　　RECONFIGURE

　　5.@Folder必须在服务器上指定个现有文件夹通过启动日期和时间个子文件夹可以由存储过程创建这就确保了创建跟踪直都是新、独无 2

　　运行探查器跟踪

　　1. 在运行你SQL代理作业的前你应该测试下存储过程

　　2. 通过带有激活"Results to Text'SSMS使用在SQL代理作业中定义命令来执行这个存储过程

　　3. 如果没有发生那么它应该正在运行要确认这点你可以执行查询"select * FROM ::fn_trace_getinfo(default)"

　　举例输出结果

　　

　　图 3

　　停止探查器跟踪

　　1. 要在它结束时间的前停止探查器跟踪你可以执行两个命令个命令停止这个跟踪另个关闭这个跟踪文件

　　2. 下面是这两个命令:

　　a. 执行"select * FROM ::fn_trace_getinfo(default)"

　　b. 使用启动这个跟踪时指定文件夹确认这个跟踪

　　c. 用#代替trace id执行这两个命令

　　EXEC sp_trace_status @traceid = #, @status = 0; -- Stop/pause Trace

　　EXEC sp_trace_status @traceid = #, @status = 2; -- Close trace and delete it from the server

　　3. 执行 "select * FROM ::fn_trace_getinfo(default)" 确认它停止了

　　4. 要确认捕捉跟踪文件你可以使用SQL Server探查器打开它们

　　5. 旧探查器跟踪文件不会自动删除你需要在完成时自己删除它们

　　结论

　　这个安排SQL探查器跟踪思路方法已经在SQL Server 20002005和2008上测试通过在第次创建时这些跟踪可能看起来有些复杂但是后来它将变成种习惯拥有个准备好跟踪无论何时发生问题你都可以节省解决问题时间