Problem: You want to schedule a trace, but SQL Profiler does not have a built-in scheduling option. So how do you use SQL Agent to schedule a SQL Profiler trace?

Solution: SQL Profiler traces are built using system stored procedures. You can use the SQL commands from an existing profiler trace and construct your own stored procedure that creates and starts a SQL Profiler trace. You will need to specify some additional settings using your stored procedure. These include run duration, file size, and trace output file location.

Create the Trace Definition

The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler. Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace. Start the trace and then stop it. Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. Note: For SQL Sever 2000 and 2008 choose the appropriate output type. Save the trace file.

Create a Profile Trace Stored Procedure

Next, take the exported trace definition and use it to create a stored procedure. Use SSMS to open the exported trace definition created above. Open another query window in SSMS and paste the trc_template stored procedure code from below.

CREATE procedure trc_Template @Folder nvarchar(200)
as
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,60,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 50 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-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 <--
-----------------------------------------------------------------------------

In the exported trace definition, find ‘– Set the events’ section. Copy all the lines until the next comment line which should be ‘– Set the Filters’. Paste these lines into the stored procedure template after the line “add the ‘– Set the events’ section below”. Next find the ‘– Set the Filters’ section in the exported trace definition. Copy all the lines until the ‘– Set the trace status to start’. Paste these lines into the stored procedure template after the line “add the ‘– Set the Filters’ section below”. Change the stored procedure name using your naming conventions. In the SQL code, the profiler stop time is set using the variable @StopTime. The current setting is for 60 minutes after the trace is started. Adjust this setting to what is appropriate. Set the @MaxFileSize to an appropriate size for the trace output file. Currently, it is set to 50 megabytes. Save the stored procedure code under a new file name other than trc__Template.sql.

Preparing the Server

Create a folder on the server that will be used to contain the profiler trace files. Enable XP_CMDSHELL on SQL Server 2005 and 2008. If you do not want to leave this feature enabled all the time, you can wrap commands around the start procedure command that enables XP_CMDSHELL, starts the trace, and then disables XP_CMDSHELL (see sample command below). Create the stored procedure on the SQL Server instance using the file that you created above. It can be created in any user database. In the example below, it has been created in the database named Admin. Create a job in SQL Agent to execute the stored procedure. Do not start the job until you have tested the stored procedure.

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

The @Folder must specify an existing folder on the server. A sub folder is created by the stored procedure using the start date and time. This will ensure that a new, unique trace is always created.

Running the Profile Trace

Before running your SQL Agent Job, you should test your stored procedure. Using SSMS with “Results to Text’ active, execute the stored procedure using the command defined in your SQL Agent Job. If no errors occur, it should be running. To verify this, execute this select “select * FROM :: fn_trace_getinfo (default)”.

Stop the Profiler Trace

To stop the profiler trace before its end time has occurred you exec two commands. One stops the trace and the other closes the trace file. Here are the commands:

Execute “select * FROM :: fn_trace_getinfo (default)”

Identify the traceid you using the folder name specified when starting the trace. Execute these two commands replacing # with the trace id.

EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the server

Verify it has stopped by executing “select * FROM :: fn_trace_getinfo (default)”. To view the captured trace files, open them using SQL Server Profiler. Old profiler trace files are not automatically deleted. You will need to delete them yourself when you are finished.

Conclusion: This method of scheduling a SQL Profiler trace has been tested on SQL Server 2000, 2005, and 2008. At first creating these traces might seem a little complex, but after time it will become second nature. Having a ready-made trace you can run whenever an issue occurs will save time in reacting to and diagnosing a problem.