Trace Flag Changes in SQL Server 2005

I recently overheard a conversation between SQL Server Engine PM Boris Baryshnikov and SQL Server MVP Linchi Shea.

Linchi was curious about the documentation of SQL Server 2005 Upgrade Advisor which states that the behavior of SQL Server 2000 trace flags may have changed, and some may no longer exist in SQL Server 2005. It goes on to recommends that you should test the behavior of any trace flags that you may be using.

Boris responded that Microsoft plans to update the Knowledge Base articles that discuss trace flags with information that's still relevant for SQL Server 2005.  So basically, you should beware if you make heavy use of SQL Server 2000 trace flags and then upgrade to SQL Server 2005.  Things may not work as you initially expected.

There are two scenarios with a trace flag on SQL Server 2000.  First, the flag is still available and unchanged.  This might be a problem in some rare situations when the underlying behavior of SQL Server 2005 is a lot different from the behavior of SQL Server 2000.  Second, the trace flag is obsolete because the flag behavior has become a part of the product or Microsoft changed internal design so much that it just no longer applicable.

You can also read more about this in the Microsoft SQL Server Setup help file under the topic of Breaking Changes:

The time at which global trace flags set by a session takes effect in other sessions has changed in SQL Server 2005 compared to SQL Server 2000. In SQL Server 2000, a trace flag set in session A does not take effect automatically in an already existing session B; instead, it takes effect ONLY after the first time any trace flag is set in session B. This non-deterministic behavior has been fixed in SQL Server 2005 to be more deterministic. In SQL Server 2005, global trace flags set in session A get set immediately in other concurrent sessions.

Oh, and there is one more change for trace flags in SQL Server 2005.  They can be specified as either local (new in SQL Server 2005) or global (as in SQL Server 2000) using an additional argument in dbcc traceon(...). If the second argument is not specified, the default value is local in SQL Server 2005, but is always global in SQL Server 2000.

Finally, when relying on trace flags in SQL Server 2005 be sure to set the trace flags using the command line wherever possible.

Hope this helps,


Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.