Tell me more ×
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.
up vote 2 down vote favorite
1

Thomas LaRock (SQL Rockstar) put up an article on how to write executed commands directly to the SQL Server Error Log without having to use Profiler (or SQL Trace). The article/video can be found here.

My question is, would this be advisable to anything beyond quick and limited troubleshooting? In other words, I can't imagine it wouldn't impact performance by keeping trace flags 4032 and 3605 set for an extended period of time.

Is it a fair statement that the instance would suffer by having all the I/O to the error log for every single statement on the instance?

share|improve this question

1 Answer

up vote 3 down vote accepted

I think the article is useful in a very limited scenario:

  • You are on a local or otherwise isolated machine.
  • You need to do this for a very short burst of activity.
  • You have no access to Profiler (not my first choice for tracing anyway), no idea how to construct a server-side trace, and no access to Books Online so you can learn the syntax (you don't need Profiler to set up a trace).
  • You have control over, or at least cursory knowledge of, the set of statements that might be executed against your instance for the duration of your "trace."
  • You have a good idea of how much space is on your C drive...

Without those disclaimers in place, I'm actually quite nervous that someone is going to skim the article, not read the comments, put this into production, and forget about it until - well, you can imagine the handful of scenarios that could result.

share|improve this answer
Thanks for the answer, Aaron, and I definitely see what you're saying. If Profiler isn't your first choice, do you just directly use the system trace functions? – Thomas Stringer Mar 3 '12 at 20:15
2  
Yes I would rather set up a server-side trace using the built-in procedures (or use our tool which supports what we call a QuickTrace :-)). Trying to make extended events second nature too, but I don't find them 100% practical on versions prior to SQL Server 2012 (depending on the scenario of course). – Aaron Bertrand Mar 3 '12 at 20:26
But if you want a long-running trace, wouldn't it make sense to offload it to a client on a different machine (SQL Profiler) to minimize performance impact on the server? – Thomas Stringer Mar 3 '12 at 20:53
2  
That depends. You can target a server-side trace to another server but depending on topology your local I/O may be better overall in terms of overhead than slamming the network. Not really an easy global answer for that. – Aaron Bertrand Mar 3 '12 at 21:03
Ok, I can definitely understand that. Thanks for the input, Aaron. It's always a pleasure and insightful to see your answers. – Thomas Stringer Mar 3 '12 at 22:16

Your Answer

 
discard

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