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
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_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?