SalvageDog (2/1/2011)
Hello,Thanks for a great article.
I have a very intermittent problem, occurs only two or three times a month. Would there be any issues with running a server side trace all day, other than creating a lot of .trc files? I'm especially concerned about an all-day trace using too many resources and affecting server performance.
As long as the trace is set up with a minimum of events and columns, and it's output to a file, you should not affect performance at all. In testing I've done, a standard set of RPC:Complete and SQL Batch:Complete events put less than 1% load on the server, regardless of load. You'll just have to deal with all the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning