Default Trace in SQL Server 2005

There have been always a few questions on keeping the default trace enabled on a SQL Server 2005 production box. Well, personally I do feel that the trace is a very handy tool with which you can find out things that have happened. This is especially true in detecting changes especially in development or testing environments where the dbas don’t have much control on the activities. By the way, tracing out the culprit will not be enough successful if there are shared logins being used. (DONT let people use shared logins; it will trouble you sooner or later). I found the default trace mostly beneficial for environments where there aren’t many experienced DBAs and there isn’t a strong or standard security measures or monitoring infrastructure. Here, the DBAs normally look back to see what happened, and only when someone created some real mess. There have been some discussions on the performance impacts of letting the default trace run on a production environment. I haven’t seen much performance benefits on my environments by disabling the trace, but still I didn’t do any load test to confirm this. There is a post from Linchi I recently found on some load test results. You can find the post at http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx?CommentPosted=true. I can’t say that Linchi's testing would be a final one, I recommend you to do your own load testing based upon your applications before coming to a final conclusion.