SQL Server Performance

How to stop Profiler Trace ??

Discussion in 'General DBA Questions' started by ykchakri, Aug 19, 2003.

  1. ykchakri New Member

    Hi,

    I want to know how to stop a profiler trace that is running through a SQL server job but not through SQL Profiler.

    I've scripted a profiler template and created/scheduled a job using this script. This script is configured to write the trace output to a file and to stop after 24 hours from the start time. Now, I want to take a look at this output file but I can't open the file as it is being used by the running profiler. So, I want to stop the profiler but I don't know what to stop beacuse the job that started the profiler is not running anymore and I don't see any process running on behalf of the profiler that I can terminate.

    The only option is left for me at this point is to wait for 24 hours when the profiler stops and releases the output file. Can anybody give me a clue how to identify the process that is running this profiler and stop it ?
  2. satya Moderator

    If that has been schedule with a job then execute SP_STOP_JOB to stop the PROFILER trace.
    I think a SP is available to stop the PROFILER, and I will post you once I findout from the library.

    In version 7 you can use xp_trace_destroyqueue and in SQL 2K sp_trace_setstatus, refer to BOOKS ONLINE for more information on both the X(s)Ps.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. ykchakri New Member

    quote:Originally posted by satya

    If that has been schedule with a job then execute SP_STOP_JOB to stop the PROFILER trace.
    I think a SP is available to stop the PROFILER, and I will post you once I findout from the library.

    In version 7 you can use xp_trace_destroyqueue and in SQL 2K sp_trace_setstatus, refer to BOOKS ONLINE for more information on both the X(s)Ps.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com


    Thanks for the suggestion Satya, But the job is triggering the profiler trace and finishing immediately. So, the job that started the profiler is not running to stop.
  4. ykchakri New Member

    I can post the script, if anybody is interested in simulating this in their environament.
  5. bambola New Member

  6. satya Moderator

    Then use as specified :

    In version 7 you can use xp_trace_destroyqueue and in SQL 2K sp_trace_setstatus, refer to BOOKS ONLINE for more information on both the X(s)Ps.

    Using SP_TRACE_SETSTATUS update the status column with 0 which Stops the specified trace..as referred in BOL.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. seasider New Member

    I have had something similar in a job that Microsoft recommended to monitor blocking.

    In the end I had to run the exact same job that created the profiler trace with the code to stop the trace, i.e. I ammended the original job code. When the job ran the second time it attached itself to the original 'ghost' execution.

    I cannot remember though whether I needed to keep the code in the job to create the profile trace or not.

    Have a play with amending the TSQL code in the original trace.
  8. gaurav_bindlish New Member

    If u don't have the trace id for the trace, usae ::fn_trace_getinfo to find the trace id and then set the status as suggested.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. ykchakri New Member

    Thanks a lot guys, That works. I've used SELECT * FROM ::fn_trace_getinfo(default) to get the traceid and then 'sp_trace_setstatus 3, 0' followed by 'sp_trace_setstatus 3, 2' which stopped the trace.

Share This Page