October 15, 2014 at 1:10 pm
If we start the serverside trace how to stop that.
I open the profiler through GUI and script out the definition and run from ssms but forgot to mention the stop time. It gave me trace ID
I want to stop after 2 hr. How can I do it now? It is running.
October 15, 2014 at 1:16 pm
You can define a specific end time when you start the trace. But it's also possible to stop the trace through code (using sp_trace_setstatus).
When the trace is running, there are several usefull commands, see code below:
-- get information about the current trace(s)
SELECT
[traceid]
,[property]
,[tpdesc] =
case
when [property] = 1 and [value] = 1 then N' produces a rowset.'
when [property] = 1 and [value] = 2 then N' creates a new file when max file size is reached.'
when [property] = 1 and [value] = 3 then N' create a new file when max file size is reached and produce a rowset.'
when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'
when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'
when [property] = 1 and [value] = 6 then N' creates a new file when max file size is reached and shuts down on an error.'
when [property] = 1 and [value] = 7 then N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'
when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.trc.'
when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'
when [property] = 4 and [value] is not null then N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'
when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'
when [property] = 5 and [value] = 0 then N' is stopped.'
when [property] = 5 and [value] = 1 then N' is running.'
end
FROM fn_trace_getinfo(0)
ORDER BY [traceid],[property] DESC
/*** ATTENTION: use the correct TRACE_ID!! ***/
-- pauze trace
EXEC sp_trace_setstatus 2, 0-- sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
-- stop trace and remove definition
EXEC sp_trace_setstatus 2, 2-- sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
October 15, 2014 at 1:26 pm
Thank you
In my running trace I can see
exec sp_trace_setstatus @TraceID,1.
In final it showed me TraceID=3
So if I run
EXEC sp_trace_setstatus 3, 2
It will stop the trace
October 15, 2014 at 1:35 pm
ramana3327 (10/15/2014)
Thank youIn my running trace I can see
exec sp_trace_setstatus @TraceID,1.
In final it showed me TraceID=3
So if I run
EXEC sp_trace_setstatus 3, 2
It will stop the trace
If your trace is running using ID 3 (check with "fn_trace_getinfo(0)") then yes, the command you state will stop that trace.
October 15, 2014 at 2:18 pm
Thank you.
Before you script the trace definition, we have to run the profiler. So there we are giving one filename. When I use the same trace file name, it wasn't allow me to use the same file name. So, I gave different file name.
October 20, 2014 at 5:33 pm
Hi,
I want to stop the running trace.
I run the command
exec sp_trace_setstatus 2,2 (My trace id=2)
Then I am getting the msg that, the active trace must be stopped before modification.
How to stop that trace?
October 21, 2014 at 8:08 am
First pauze the trace, then stop it. Stopping it will also remove the trace definition.
-- pauze trace
EXEC sp_trace_setstatus 2, 0-- sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
-- stop trace and remove definition
EXEC sp_trace_setstatus 2, 2-- sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply