Sql-server – Trace To the Error Log

sql server

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?

Best Answer

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.

Related Question