Can i schedule profiler into sql server

  • I can see only Enable trace stop time in gui of profiler.

    i need to run the profiler in midnight 12 to 6 in the morning. in this case i can only schedule it for the stop time not for the start time?

    any wayout please....

    Cheers,

    DKG

  • You can start a trace without using the Profiler GUI. See my replies in this thread describing how to create, start and stop a server-side trace: http://www.sqlservercentral.com/Forums/Topic529790-5-1.aspx

    Greg

  • Greg, thanks for your input on this.

    I have gone through the article and long discussion with in the article.

    Seems useful, the script mentioned in the article is the output of Profiler->File->Export->Script Trace Definition

    but when i ran it within query analyzer to test whether it works fine or not it failed with error

    The trace file name is not valid

    Now which file and location needs to be specified for trace location. Even I’ve tested to create a physical location e.g. D:\test\test1.trc

    When I specified this, even its not working....Could you clear my confusion?

    Please also let me know by example how to stop the trace on specified date and time.

    Cheers,

    DKG

  • When you open the script you should reset the file location, you can use a file share. You can define the stop time as the 5th parameter to sp_trace_create.

  • Jack,

    this peice of code contains sp_trace_create and the file name:

    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

    if (@rc != 0) goto error

    Now please let me know if i want to stat it at '28/Jul/08 23:00' and want to stop at '29/Jul/08 05:00' how the statement would be and my other confusion is for file name, its now working by any way. Please make it clear to me.

    I have specified the physical location like d:\test\test.trc, now this test.trc should exists or not if exist how this would be created?

    Thanks,

    DKG

  • The trace file name is nvarchar(245). Did you specify N'D:\test\test1.trc? You can set the stop time using the @stoptime parameter of sp_trace_create or you can use sp_trace_setstatus to stop it.

    Greg

  • Greg,

    yes i have specified exec @rc = sp_trace_create @TraceID output, 0, N'C:\trace_dev_test.trc', @maxfilesize, NULL

    if (@rc != 0) goto error

    where trace_dev_test.trc file exists.

    in first run it gives trace id 3 as a result and in second run it gives

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

    Cannot create a new trace because the trace file path is found in the existing traces.

    error.

  • You only need to create the trace once.

    Once you create the trace, put sp_trace_setstatus trace_id, 1 in a job sceduled to run at 11:00 PM and put sp_trace_setstatus trace_id, 0 in a job scheduled to run at 5:00 AM.

    The output file won't be created until the trace starts.

    Greg

  • Ok, means all i need to create a trace file on some specific location and two sql agent jobs one would have sp_trace_setstatus trace_id, 1 - job having schedule to run at 11:00 PM

    Other job would have sp_trace_setstatus trace_id, 0 - job having schedule to run at 5:00 AM next day.

    Please let me know if still i am missing something 🙂

  • Looks right to me. BTW, the 2 option is sp_trace_create specifies that a new trace file will be created when the current file reaches it's max file size (which you can also specify), so if there's a lot of activity, don't be surprised to find several trace files.

    Greg

  • Looks good to me. By doing it this way the trace definition remains available and you can run it whenever you need.

    You can check the traces that are defined by querying sys.traces.

  • Thanks Greg and Jack for your time and valuable suggestion it worked this time. Trace started automatically.

    But I think ive done something wrong with the trace stop job. what i did i have copied the same code in the stop job with replacement of sp_trace_setstatus trace_id, 0

    It failed at stop time with error ::

    Executed as user: NT AUTHORITY\SYSTEM. Cannot create a new trace because the trace file path is found in the existing traces. [SQLSTATE 42000] (Error 19067). The step failed.

  • Your stop job should only have sp_trace_setstatus in it, not the sp_trace_create part. If you run that again it's like you're trying to create the same trace again.

    Greg

  • Greg,

    This time ive supplied

    exec sp_trace_setstatus 0 or tried with exec sp_trace_setstatus '0' also

    in the stop job, now it says

    Message

    Executed as user: NT AUTHORITY\SYSTEM. Procedure or Function 'sp_trace_setstatus' expects parameter '@status', which was not supplied. [SQLSTATE 42000] (Error 201). The step failed.

  • Greg/Jack,

    Your help would be appriciated...

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

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