stop a Profiler or SQL Trace using sp_trace_setstatus

http://weblogs.sqlteam.com/dang/archive/2007/12/16/Avoid-Causing-Problems-with-Profiler.aspx

 traces are ultimately created and managed using documented stored procedures (sp_trace_create, sp_trace_setevent, sp_trace_setfilter and sp_trace_setstatus).  However, instead of calling these stored procedures directly, SQL 2005 Profiler uses the SMO API which wraps these stored procedure calls and well as calls to an undocumented procedure to retrieve trace data.


http://www.eggheadcafe.com/software/aspnet/31538555/how-to-kill-a-profiler-trace.aspx

You can stop a Profiler or SQL Trace using sp_trace_setstatus.

Dan Guzman posted on Wednesday, January 23, 2008 10:19 PM

You can stop a Profiler or SQL Trace using sp_trace_setstatus. First, determine the desired trace id: SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2; Then, specify the trace id in the script below to stop and delete. DECLARE @TraceID int SET @TraceID = ? EXEC sp_trace_setstatus @TraceID, 0 EXEC sp_trace_setstatus @TraceID, 2 -- Hope this helps. Dan Guzman SQL Server MVP