Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted
  1. 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"

  2. Overwritten. The 1, 2, etc suffix is for rollover: not for trace startup

  3. 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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.