Remove SQL Server Trace messages from Error Log

It is common practice for a DBA to run Profiler/Server-side traces in order to troubleshoot performance issues. At times some server-side traces are also run for Auditing purposes. Whenever a SQL Server Trace is started or stopped, the following messages are logged in the SQL Server Error Log.

SQL Trace ID 2 was started by login "loginname"
SQL Trace stopped. Trace ID = '2'. Login Name = 'loginname'.

This is an informational message. But if the traces are started and stopped frequently, these messages can really bloat the SQL Server Error Log. I have seen a couple of instances in my environment which have these messages every 5 minutes. The relevant information can be easily searched in the SQL Server Error Log as I had explained here. But if these messages are causing the SQL Server Error Log to grow and thereby consuming a lot of drive space, then the DBA has to think of some alternative.

The easiest alternative is to stop these informational messages from getting logged in the SQL Server Error Log. Fortunately SQL Server 2005 onwards, the trace flag 3688 has been included. Starting the SQL Server with this trace flag on, the trace related messages do not get logged in the Error Log. Here is how to add this trace flag in SQL Server Configuration Manager.

After adding this trace flag, the SQL Server service needs to be restarted for the changes to take effect. This trace flag will stop all trace related messages (including the default trace) from appearing in the SQL Server Error Log.

7 thoughts on “Remove SQL Server Trace messages from Error Log

  1. Pingback: Tweets that mention Remove SQL Server Trace messages from Error Log | SQL Server DBA Diaries of Pradeep Adiga -- Topsy.com

  2. Pingback: Remove SQL Server Trace messages from Error Log | SQL Server DBA … - sql

  3. Pingback: FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) | SQL Server DBA Diaries of Pradeep Adiga

Leave a Reply