Scheduling a SQL Server Profiler Trace

By:   |   Comments (12)   |   Related: > Profiler and 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"

    paste output from trace here
     
  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"

    paste output from trace here
     
  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
    
  5. 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

Profiler traces running

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author David Bird David Bird has years of IT Experience working as a DBA and programmer on Windows, UNIX, and mainframes.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, February 17, 2022 - 11:06:10 AM - Cris Back To Top (89806)
I came across a file on my computer titled "trace database" saved as a text document. I'm confused at what I'm looking at. Is this something that someone has put on my computer to track my moves?

Thursday, October 3, 2019 - 8:31:32 AM - S C Shahaney Back To Top (82657)

Hi David

I had created the stored procedure as guided in this tip. After running the stored procedure getting the following error 

Msg 217, Level 16, State 1, Procedure trc_Brown, Line 20

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Thanks


Friday, March 10, 2017 - 9:15:04 AM - Alexander Back To Top (47745)

 

great stuff, works like a charm.

mny thnx for the post!!

 


Tuesday, November 4, 2014 - 9:34:14 AM - Balasubramanian Back To Top (35180)

i want to trigger/call  a webaddress for every one hour  in  sqlserver2005, how plz suggest the code


Friday, August 8, 2014 - 11:29:06 AM - SteveG Back To Top (34063)

Great article, but I have a problem.  When I execute the stored procedure to run the trace in a query window, it runs fine, but when I execute it in a job, I get this error: The system cannot find the file specified.  The job is executing using the SQL agent account which is a windows admin and a sysadmin.  I've checked the permissions to the file path, and administrators have full control.  Any ideas?

 

 


Wednesday, January 8, 2014 - 11:17:09 PM - Tomasz Back To Top (27998)

Fantastic article! I have small problem with security on the file (had to add Everyone with Read access) but whole process is working like a dream! Thnx!


Tuesday, July 9, 2013 - 5:14:37 PM - Omar Luque Back To Top (25766)

Thanks David,

I can not see the trace file with Sql Server Profiler, i get the following error message:

can not open file.

Access denied.

Thanks.

 

Omar Luque Valdivia.

Lima-Peru


Wednesday, May 29, 2013 - 2:49:42 AM - Special Back To Top (25176)

I want to trace for 30 minutes.How set for this?

Please let me know ?


Tuesday, March 29, 2011 - 7:37:37 PM - David Bird Back To Top (13372)

When generating the trace, select save in table. I have never tried it because trace files work better for me.  I have read about setting up stored procedures that are executed at the startup. A stored procedure can be used to start a sql agent job which starts a SQL profile trace. I would not recommend running profile traces 24x7.

Your question on working on blocking and cpu intensive SQLs is an article in itself. You might try searching for such articles or possible book on the subject.  I often use the DMV's to find the most CPU intenstive queries and use profile traces to collect details about it.

 

 


Thursday, March 24, 2011 - 6:15:04 PM - subhash Lahoti Back To Top (13314)

The artical is great.

could  you inform How can I save results to a table in a database and not to a trc file? and leave running as a Startup job. Also  Could you provide how to work on blocking and cpu intensive sqls


Monday, January 24, 2011 - 11:04:34 PM - Fernando Jacinto Back To Top (12693)

How can I save results to a table in a database and not to a trc file? and leave running as a Startup job


Wednesday, July 8, 2009 - 11:44:02 AM - cferdig Back To Top (3709)

Anyway to set the server you want to profile or do you have to run the proc on the profiled server? Like if I open profiler on machine B i can select machine A as the one I want to trace, but I cannot save this connection information.















get free sql tips
agree to terms