Scheduling a SQL Server Profiler Trace

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.  Instructions on how to create, execute, and stop the profiler trace using this stored procedure are covered in detail.

Create the Trace Definition

The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler.

1. Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
2. Start the trace and then stop it.
3. 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.
4. Save the trace file.

Create a Profile Trace Stored Procedure

Next, take the exported trace definition and use it to create a stored procedure.

1. Use SSMS to open the exported trace definition created above.
2. Open another query window in SSMS and paste the trc_template stored procedure code from below.

   /*
   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'
   */
   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 <--
   -----------------------------------------------------------------------------
   -- This filter is added to exclude all profiler traces.
   exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'
   -- Set the trace status to start
   exec sp_trace_setstatus @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_setstatus @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'
   select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'
   return
   go
   

3. 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’
4. Paste these lines into the stored procedure template after the line “add the ‘– Set the events’ section below”

   
   

5. Next find the ‘– Set the Filters’ section in the exported trace definition. Copy all the lines until the ‘– Set the trace status to start’
6. Paste these lines into the stored procedure template after the line “add the ‘– Set the Filters’ section below”
   
   
7. Change the stored procedure name using your naming conventions.
8. 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.
9. Set the @MaxFileSize to an appropriate size for the trace output file. Currently, it is set to 50 megabytes.
10. Save the stored procedure code under a new file name other than trc__Template.sql

Preparing the Server

1. Create a folder on the server that will be used to contain the profiler trace files.
2. 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).
3. 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.
4. 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

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

Stop the Profiler Trace

1. 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.
2. Here are the commands:
   1. Execute “select * FROM ::fn_trace_getinfo(default)“
   2. Identify the traceid you using the folder name specified when starting the trace.
   3. 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
3. Verify it has stopped by executing “select * FROM ::fn_trace_getinfo(default)“
4. To view the captured trace files, open them using SQL Server Profiler.
5. Old profiler trace files are not automatically deleted. You will need to delete them your-self when you are finished.

Attached Files

The ZIP file attached to this article contains the stored procedure template, SQL scripts, and a couple of sample traces. One sample trace captures login failures; which is very useful on SQL Server 2000 because it identifies the client name and IP address. The second collects performance data use by the Database Index Advisor for making recommendations.

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 get to 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.

Next Steps

• Create a collection of profile traces you can run when needed
• Use Database Tuning Advisor to review your performance traces
• Additional Resources:
  • SQL Profiler Command Line Options
  • File Attachment