How to Schedule a SQL Trace ?

  • All,

    I need to schedule a sql trace on Profiler.I see a stop time but no start time.Accordin to msdn I can schedule a Trace by the SQL Profiler GUI or as a job via SQL Server Agent.How do I do this,I need to execute the trace while my monthly subscription runs on the begining of each month roughly for 5 min.How can I achive this ?

  • Get profiler to script the trace. (File - Export). Put the script in a SQL job, make the couple of required changes (specifying a filename and the end time) and then schedule the job to run when you need it to.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Im trying right now.Shall update if I face any problems.

    Thank You

  • This is what I have done.

    Go to SQL Profiler > Create new Trace>

    Specify Trace stop time and file name to save the trace to.

    The Trace would start executing.Go to File >Export >Script Trace

    Defination> SQL 2005

    Save the Scripted trace as .sql.

    Open Mngt Studio > New Query.Open the .sql file .Edit the following two lines

    set @DateTime = '2008-10-17 11:00:00.000' [Script End Time]

    exec @rc = sp_trace_create @TraceID output, 0, N'C:\1.trc', @maxfilesize, @Datetime [script file location]

    Create a new sql job of type TSQL and copy the scripted trace and schedule a start time.

    I have tested the sql job by running it.The job executed at the schedule start time but did not continue executing till the scripts end time.Instead executed and stopped within a sec.

    The .trc file that it had to save the trace to was also empty.

    Am I missing soing something wrong here?

  • You may want to set the end time to be an offset of the time the script runs. Something like

    SET @DateTime = DATEADD(hr,1,GETDATE())

    if you want the trace to run for an hour

    Run it in management studio before you put it into a job. You'll see errors in management studio, you won't see them in a job

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Should the trace use Master db to run ?

  • Doesn't matter what database it's in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • LOOKUP_BI (10/17/2008)


    All,

    I need to schedule a sql trace on Profiler.I see a stop time but no start time.Accordin to msdn I can schedule a Trace by the SQL Profiler GUI or as a job via SQL Server Agent.How do I do this,I need to execute the trace while my monthly subscription runs on the begining of each month roughly for 5 min.How can I achive this ?

    best way is via a server side trace, the 4 stored procedures you need are

    SP_TRACE_CREATE

    SP_TRACE_SETEVENT

    SP_TRACE_SETFILTER

    SP_TRACE_SETSTATUS

    also the functions

    fn_trace_gettable

    fn_trace_getinfo

    to generate a trace definition use the following syntax

    declare @traceidout int

    declare @maxfilesize bigint

    declare @on bit

    set @on = 1

    set @maxfilesize = 50 --size in MB's

    exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULL

    exec sp_trace_setevent @traceidout, 12, 1, @on

    exec sp_trace_setevent @traceidout, 12, 3, @on

    change @maxfilesize to whatever value you require (its in MB)

    Dont append the .TRC to the path above it does it for you.

    refer to BOL for all trace events and columns

    set a filter using the following syntax

    exec sp_trace_setfilter @traceidout, 3, 0, 0, 7

    use the following to get your trace details and ID

    select * from ::fn_trace_getinfo(default)

    use the following to start, stop and close the trace

    (must stop a trace before it can be closed. Must be closed before you can access the file)

    exec sp_trace_setstatus TRACEID, 1 --start trace

    exec sp_trace_setstatus TRACEID, 0 --stop trace

    exec sp_trace_setstatus TRACEID, 2 --close trace

    finally to output to a table stop and close the trace then use the following syntax

    SELECT * INTO temp_tablename

    FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)

    You can create SQL jobs and schedule them using the code above. As it runs server side there are no I\O nasties that a client would generate and you can schedule it at will

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (10/18/2008)


    use the following to start, stop and close the trace

    (must stop a trace before it can be closed. Must be closed before you can access the file)

    Just to be clear - you don't have to stop a server side trace to access the trace file.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'm trying to create a trace script like the one above having exported the SQL from Profiler. However, whatever path I choose for the trace file I get the following error:

    Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(error not found).

    Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1

    Could not create a trace file.

    I suspect this could be because the server hasn't got write access to the drives I'm using but I can't work out how to check this. Any help would be appreciated!

  • can you post the create trace statement you are using

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Yep, here you go:

    /****************************************************/

    /* Created by: SQL Server Profiler 2005 */

    /* Date: 11/06/2009 11:53:22 */

    /****************************************************/

    -- 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'c:\trace_test', @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, 15, @on

    exec sp_trace_setevent @TraceID, 10, 31, @on

    exec sp_trace_setevent @TraceID, 10, 16, @on

    exec sp_trace_setevent @TraceID, 10, 48, @on

    exec sp_trace_setevent @TraceID, 10, 17, @on

    exec sp_trace_setevent @TraceID, 10, 10, @on

    exec sp_trace_setevent @TraceID, 10, 18, @on

    exec sp_trace_setevent @TraceID, 10, 35, @on

    exec sp_trace_setevent @TraceID, 10, 12, @on

    exec sp_trace_setevent @TraceID, 10, 60, @on

    exec sp_trace_setevent @TraceID, 10, 13, @on

    exec sp_trace_setevent @TraceID, 10, 6, @on

    exec sp_trace_setevent @TraceID, 10, 14, @on

    exec sp_trace_setevent @TraceID, 12, 15, @on

    exec sp_trace_setevent @TraceID, 12, 31, @on

    exec sp_trace_setevent @TraceID, 12, 16, @on

    exec sp_trace_setevent @TraceID, 12, 48, @on

    exec sp_trace_setevent @TraceID, 12, 1, @on

    exec sp_trace_setevent @TraceID, 12, 17, @on

    exec sp_trace_setevent @TraceID, 12, 6, @on

    exec sp_trace_setevent @TraceID, 12, 10, @on

    exec sp_trace_setevent @TraceID, 12, 14, @on

    exec sp_trace_setevent @TraceID, 12, 18, @on

    exec sp_trace_setevent @TraceID, 12, 35, @on

    exec sp_trace_setevent @TraceID, 12, 12, @on

    exec sp_trace_setevent @TraceID, 12, 60, @on

    exec sp_trace_setevent @TraceID, 12, 13, @on

    -- Set the Filters

    declare @intfilter int

    declare @bigintfilter bigint

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 21831f93-dde4-4674-b27e-8a2b08b0c1a0'

    set @bigintfilter = 1000000

    exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

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

  • You're trying to write the trace file in the root of C:.

    I remember that on a lot of server systems you need special permissions to create files there. Try using a path like C:\temp\Trace.trc

    [font="Verdana"]Markus Bohse[/font]

  • i agree with Markus. Create\use a folder that you have permissions for and use that

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks very much for your help on this chaps. Markus' suggested file location has certainly cured the error message. Excuse me if this is a daft question but I can't work out how to get at the trc file the code has created. I'm presuming the c drive referred to is the c drive for the server & not for my own machine but I can't work out how to find it.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply