April 24, 2014 at 9:27 am
I wanted to schedule a trace to run at a particular time so I create a script using profiler and executed on my server. This created the trace fine, I could see it in sys.traces and I was able to start it with sp_trace_setstatus. However, after a period of time I noticed that the trace does not exist anymore. At no point am I setting the trace status to 2.
What could be removing the trace?
April 24, 2014 at 9:39 am
The SQL Server log should contain information about the trace start/stop events.
Start from there.
-- Gianluca Sartori
April 24, 2014 at 9:54 am
I filtered the log file to find the word 'Trace'. The only entries I can see are start and stop messages for the trace which were generated by me. Nothing to say the trace has been removed.
April 24, 2014 at 9:58 am
Unless you modified the script which profiler generated, it will have a file size of 5MB and no rollover set. That means as soon as the trace file reaches 5MB, the trace will automatically stop and the definition will be removed from the server.
If you want to trace longer, you need to increase the file size and/or set it to use rollover files. Consider setting an end date as well for the trace, unless you want it to run until you stop it or it uses up all allowed space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2014 at 10:03 am
Yes I have both set - file size is set to 500MB and the end date is offset by 2 hours.
The thing is, it's getting removed before I even start it. Yesterday I created it and ran it a couple of times to make sure it was ok. But today I just created it and didn't touch it. I checked sys.traces a couple of hours later and it was gone. Server was not restarted either.
April 24, 2014 at 10:09 am
Oh wait, I think I see what's wrong.
When I create the trace I am setting the stop time to DATEADD(hh, 2, GETDATE()).
This is configuring the stop time relative to the time I create the trace, whereas I thought it was relative to the time the trace is started.
If the stop time is set does it remove the trace regardless of it's state?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply