profiler加入计划任务

创建profiler的存储过程：

USE [xxxDB]
GO

/****** Object:  StoredProcedure [dbo].[CreateProfile]    Script Date: 2015/12/16 17:23:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE proc [dbo].[CreateProfile]
as

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @tracefile nvarchar(256)
set @maxfilesize = 500 
--设置最大跟踪文件的大小500M
set @tracefile = 'd:\tracefile\'+convert(char(8),getdate(),112)
-- 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, @tracefile, @maxfilesize, NULL 
-- 创建跟踪
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
--设置跟踪事件
-- sp_trace_setevent [ @traceid = ] trace_id  , [ @eventid = ] event_id , [ @columnid = ] column_id , [ @on = ] on 其中on=1 开启 on=0 停止 on=2 关闭 
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 8, @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, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 49, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 49, @on


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

set @bigintfilter = 4000000
-- duration的最大时间

exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- 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

结束profiler的存储过程（profiler查询消耗的是系统内存）：

Create proc [dbo].[StopProfile]
as

declare @traceid int

select @traceid = id
from sys.traces
where path like 'd:\tracefile%'  
-- path 为跟踪文件的存储路径

exec sp_trace_setstatus @traceid,0

exec sp_trace_setstatus @traceid,2

具体profiler的开启关闭的时间，可以在sqlserver代理的作业中根据需要更改。