Monday, April 22, 2013

Scheduling SQL Profiler trace as SQL Agent job

Connect to the server using management studio and go to Tools and Launch SQL Server Profiler.
Connect to the server you want to run the trace on. 


Go to Event Selection tab and make check Show all events and Show all comments. You'll see the events divided in various nodes and any nodes with selected Events will show up in bold. Make sure you expand each node and select the events you really need and remove the ones you don't. Profiler traces will generate a lot of log so use discretion while selecting events.



If you would like to restrict your trace to a single database, you can use column filters. Click on Column filters and navigate to the filter Database Name. For this post, I set it to AW2012.



Start the trace by clicking Run and stop it immediately. 


This will enable us to generate a trace script that we will later modify a bit to schedule it as a SQL Agent job. Save the exported file and open it with your favorite code editor. It will look something like this



-- 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, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
-- and few other trace events
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 91b76b3b-83a8-4d12-80c5-4b611bfc3894'
-- 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

The script uses sp_trace_create extended proc to create trace. In order to schedule this script as a job, we need to make sure that we can control the output trace file size and file name and duration of trace run. The example usage of sp_trace_create is below:
sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value 
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]

The @options parameter decides the trace_file_rollover behavior. If you set the max file size to 100 MB and set trace_file_rollover to 0, the trace can not continue running after filling up 100 MB in the output file. You can set its value to 2 which will make SQL Server to close the existing trace file and create a new one. For this demo, I am going to set it to 0 and set a trace file size in advance.

The changes we are going to make to the trace script generated above - declare a tracefile name and set it to create unique files based on getdate(). If we are scheduling it as a job, we will likely run it multiple times. If the filename is same during all runs, the trace will fail stating that the tracefile already exists. @datetime decides how long this trace will run so we will set it to run 60 min once invoked using dateadd. The script looks like this:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @tracefile nvarchar(256)
SET @tracefile = 'D:\TraceData\'+ convert(CHAR(8), getdate(), 112) + '_' + cast(replace(convert(VARCHAR(5), getdate(), 108), ':', '') AS CHAR(4))
SELECT @tracefile

set @DateTime = dateadd(mi, 60, getdate())
set @maxfilesize = 3000


exec @rc = sp_trace_create @TraceID output, 0, @tracefile, @maxfilesize, @Datetime
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on

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

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8d1fe722-9d94-4c31-bfaf-97d07656326b'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'cart'
exec sp_trace_setfilter @TraceID, 35, 0, 1, N''
-- 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


Now this script can be scheduled as a SQL Agent job. You might want to enable xp_cmdshell as a first step, run the trace script and disable xp_cmshell as this process involves creating trace files etc., If you need to enable a developer run this trace job on demand while you are away, you can create a stored procedure

create procedure [public].[usp_runTheTraceJob]
with execute as owner

exec sp_start_job 'TheTraceJob_Name'
go

The dev must have execute permissions on the stored procedure and also should be a member of one of these roles in msdb: SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole