I've successfully started some server-side tracing on a customer's SQL Server instance thanks to a few great posts out there, but since that's really my first ever in a "real-life" situation, I was wondering about a few things.
1) I've defined a trace file's "base" name in the script that creates the trace. I'm starting the trace at 6am and turning it off again at 7pm everyday. What I'd really love to do is set the trace file's base name to include the date, e.g. I would love to make it be
(some directory)\MyTrace-2012-Apr-19
and so forth – so that each day's trace files would be easily recognizable. Is there a way to achieve this?? Would I have to actually create and drop the trace every day to get this??
2) If I turn on and off the trace on a daily basis, will my trace file from yesterday (MyTrace.trc) be reused today, up to its defined max size? Or will SQL Server create a new trace file (MyTrace_1.trc, MyTrace_2.trc) every day when it starts up?
3) Also, I had trouble trying to go get that trace file, even after 7pm – it seems as if it was still locked by some process and I couldn't just copy it elsewhere… I was under the assumption that once the tracing is stopped, the file would be "freely" available – am I wrong?
Best Answer
You'd have to substitute the datetime in the sp_trace_create
That is, drop and create each day unfortunately (as you noted). There is no "alter trace"
Overwritten. The 1, 2, etc suffix is for rollover: not for trace startup
It isn't locked.
The NTFS ownership and permissions are not inherited from the parent folder. You need to change this using Explorer or via scripting (cacls, subinacl) (not sure exactly sorry, been some time)