SQL Server Performance Forum – Threads Archive

How to stop Profiler Trace ??

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 ?

---

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.

---

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

---

Check BOL for sp_trace_setstatus.
You might also want to take a look at this article http://support.microsoft.com/defaul…port/kb/articles/q283/6/96.asp&NoWebContent=1 Bambola.

---

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

---

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.

---

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

---

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.

---

]]>