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代理的作业中根据需要更改。