Home > T-SQL Reference, Trace Flags > A Topical Collection of SQL Server Flags

A Topical Collection of SQL Server Flags

[Edit 2016-04-27: Updated through SQL 2014 RTM CU13 and 2014 SP1 CU6, along with some flags first seen in posts from major bloggers.]

[Edit 2015-06-23: Updated through SQL 2014 RTM CU8 and 2014 SP1 CU1, along with some cleanup.]

[Edit 2015-01-21: A number of new flags have been added to the document due to finding new TFs in several PASS 2014 sessions and sweeping through the CU KB articles from SQL 2005 to the present. The document is still in need of much cleanup and reorganization, however.]

[Edit 2014-11-04: I had wanted the third release of this document to essentially be “complete” (only needing the ongoing maintenance of finding new flags and referencing KB articles/blog posts). However, I still have quite a bit more to do and there are enough new flags & updates that publishing an interim revision “only” 6 months after the last one seems worthwhile.]

[Edit 2014-04-27: The second release of this document features improved categorization, consistent formatting, a more formal introduction, and the inclusion of a large number of flags from Paul White (both his SQLBlog.com and SQLPerformance.com blogs), Ben Nevarez, Dima Piliugin, and KB articles.]

I hate forgetting things. I also love exploring the deepest corners of whatever I’m involved in, which currently includes SQL Server. These 2 personality quirks do NOT go together unless one has a good system for organizing notes, and so about a year ago I bit the bullet and finally started a system for organizing the technical content that I devour. One of my collections is a “Trace Flag Repository”, organized by topic (or “area of the Engine”, if you prefer).

Trace Flags are admittedly one of the geekier areas of SQL Server, especially as so many of them are undocumented by Microsoft and control obscure, little-understood areas of the product. I generally find value from perusing trace flag information in one of the following ways:

  • Informational flags give me tools to “trace” a specific area of the product to a level of depth not covered by Profiler, or maybe even Extended Events.
  • Obscure, behavioral trace flags often illustrate a design choice that Microsoft faced, and help me to understand where the default behavior of the product might run into problems.
  • (Very rarely) I find a trace flag that actually promises to alleviate some annoyance I’ve encountered with the product, though is not yet documented by Microsoft (I’m thinking of TF 3226, suppressing BACKUP messages to the SQL Log, which was not documented until SQL 2008)

At any rate, whether I’m looking through trace flags from geeky interest or searching for something helpful, the existing repositories of undocumented trace flags online have been mostly large, unorganized lists. Thus, I decided to start putting my own together by starting with the SQL Server Central list and categorizing by areas of the DB engine (e.g. SQLOS, Disk IO, Query Optimizer, etc). I’ve since been adding flags that I discover in blogs, whitepapers, or even print, and will continue to incorporate flags as I stumble onto them in KB articles, blog posts, Connect items, and such.

My ultimate goal is to have an intuitive categorization of flags (as many as is possible/reasonable), each with relevant links to trustworthy whitepapers/blogs/etc. If you see flags that are not present in the document, or have links to informative content or useful application for a specific flag, please post below in the comments and I’ll incorporate into the document. The primary audience for the attached PDF was originally “me”, but I certainly want this to be helpful to the SQL Server community at large, and welcome your feedback!

Enjoy…

[Edit 2014-11-04: The immediately-previous version is retained so that readers can compare the additions/changes between the current & previous documents]

SQLCrossJoin_TraceFlagRepository_v6 (pdf)

SQLCrossJoin_TraceFlagRepository_v5 (pdf)

Advertisements
  1. April 22, 2014 at 3:32 pm

    Trace flag 4121: Works together with 4101 to resolve this issue. http://support.microsoft.com/kb/942444

  2. April 22, 2014 at 3:34 pm

    T699 – indicated as disabling transaction logging. May be a holdover from Sybase. Tested on SQL Server 2012, no discernible effect on transaction logging behavior.

  3. April 22, 2014 at 3:35 pm

    Two blog posts of mine to collect the (documented) fixes contained in T4199:
    http://bit.ly/1kh3GtM
    http://bit.ly/1iaNdCV

  4. April 24, 2014 at 10:15 pm

    Another one for you – T4134 that addresses some wrong results and primary key error conditions with parallel plans.

    http://support.microsoft.com/kb/970198
    FIX: You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008 or in SQL Server 2008 R2

    http://support.microsoft.com/kb/2546901
    FIX: Results may change every time that you run a parallel query in SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2 if the query uses a ranking function and if the computer has eight or more CPUs

    http://connect.microsoft.com/SQLServer/feedback/details/634433/parallel-insert-plan-causes-primary-key-violation
    Parallel insert plan causes Primary Key Violation
    “… see the following KB: http://support.microsoft.com/kb/970198. Even that the symptom described in that KB is different, the root cause is the same. Please, note the mentioned Hotfix requires special trace flag 4134 to be enabled in order to take effect.”
    “The reason for protecting a change by a trace flag is to make sure we do not regress any other cases if the change may affect query plan choice. After some time, when we see stable behavior after the Hotfix, we remove trace flag protection for some fixes and make them active by default. For this fix, it may happen for future Cumulative Updates and the next Service Pack.”

  5. April 27, 2014 at 3:15 pm

    Thank you sir! I appreciate your feedback, keep it coming. 🙂

    I’ve just now published an updated version of the document. All of the flags that you mentioned have been incorporated in some fashion or another.

    Ciao,
    Aaron

  6. October 21, 2014 at 7:58 am

    Trace flag 1851 disables the automerge functionality for in-memory oltp.

    From Microsoft In-memory oltp presentation at sql relay reading 2014, slides should go up at http://www.sqlrelay.co.uk/resources/sql-relay-2014-slides.html.

    • October 30, 2014 at 3:11 pm

      Thanks Dave! Good find.

  1. April 1, 2015 at 5:56 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: