Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Scheduling a SQL Server Profiler Trace

MSSQLTips author David Bird By:   |   Read Comments (7)   |   Related Tips: More > 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


Last Update: 3/25/2009


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.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, July 08, 2009 - 11:44:02 AM - cferdig Read The Tip

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.


Monday, January 24, 2011 - 11:04:34 PM - Fernando Jacinto Read The Tip

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


Thursday, March 24, 2011 - 6:15:04 PM - subhash Lahoti Read The Tip

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


Tuesday, March 29, 2011 - 7:37:37 PM - David Bird Read The Tip

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.

 

 


Wednesday, May 29, 2013 - 2:49:42 AM - Special Read The Tip

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

Please let me know ?


Tuesday, July 09, 2013 - 5:14:37 PM - Omar Luque Read The Tip

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, January 08, 2014 - 11:17:09 PM - Tomasz Read The Tip

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!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.