July 30, 2008 at 1:35 pm
You have to provide sp_trace_setstatus 2 parameters. Parameter 1 is the traceid and parameter 2 is the status. So you need:
sp_trace_setstatus @traceid = traceid, @status = 0
to stop a trace,
sp_trace_setstatus @traceid = traceid, @status = 1
to start a trace, and
sp_trace_setstatus @traceid = traceid, @status = 2
to stop a trace and remove the definition from the server.
You can get the traceid by querying sys.traces.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 4:25 pm
Jack,
is that the stop job would contain just below statement(to stop the trace):
exec sp_trace_setstatus @traceid = traceid, @status = 0
July 30, 2008 at 4:35 pm
Jack,
It worked fine 🙂 the job stopped
ive query sys.traces and get the trace id and passed it into job....thanks a lot
Now if i want to split the trace files like we can set it into gui ->Set Maximum file size(MB) = 100/200 e.g.
can i pass it - in place of @maxfilesize. Would it be in MB bydefault and like '100'
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
please suggest....
July 30, 2008 at 7:13 pm
I'm glad we got you up and running. Now it's time for you to dive into BOL (Books On Line) and read up on sp_trace_create. It has all the information you need to set the file size and number of rollover files.
We are now in "Give a man a fish and you feed him for a day, teach a man to fish and you feed him for his life" territory.
Look it up and if there is something in BOL you don't understand post it an we'll help you understand it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 1:16 am
Thanks Greg and Jack for all your help!!!
August 1, 2008 at 11:59 am
this may help
How to run profiler in batch mode:
start profiler and create new trace -- click - save to file, roll over & max. file size 100 meg then select evets and run it for few
minutes and stop it.
1) file/save or export script trace defination for sql200/5 (creates *.sql file) and edit this file these -
a) set @maxfilesize = 100 b) InsertFileNameHere to 'e:\sqlbku\monitor\file_name'
2) Run/schedule this file in query analyzer and note the trace id to stop this trace later e.g. traceid = 1 .
3) To stop the trace edit *.sqlfile exec sp_trace_setstatus @TraceID, 1 to exec sp_trace_setstatus 1, 2 and run this command in saparate
query analyzer exec sp_trace_setstatus 1, 2 to stop the trace.
research sp_trace_create
August 1, 2008 at 12:07 pm
Thank you very much Himmat for your input.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply