THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Document more trace flags?


It’s been over 5 years since I last blogged about trace flags, so it seems a post on the topic is in order.

If a trace flag is undocumented, it means it is unsupported and not guaranteed. It can go away at any time, even from one service pack to the next. If a trace flag doesn’t work the way the person who told you about said it should work, there is no help for that. It is undocumented.

Trace flags change SQL Server’s behavior. They are toggles, that can be turned on or off. When they are on, they force SQL Server to do things differently. Sometimes just a little bit differently, sometimes a LOT differently. Some are very harmless, such as 3604 which allows SQL Server to return output from certain undocumented DBCC commands to return output to the client. You need to use this flag if you’re going to use the DBCC PAGE command, which I talk about a lot in my writings and in my SQL Server Internals classes.

However, there are other trace flags that change SQL Server behavior a LOT. The SQL Server developers at Microsoft create trace flags for lots of reasons, usually to force some sort of non-standard behavior. This might be so the developers can test the feature during development, or so that the support engineers can turn a feature off – either to confirm that the feature was causing a problem. These are usually not changes that you would want to make to your own SQL Server in a normal environment.

But with so many undocumented trace flags, the question frequently arises “Why aren’t MORE trace flags documented?”

Here are some of the reasons:

-- Once a trace flag is official publicly documented, people will start to use it. And people will also start to misuse it. Microsoft needs to consider whether dealing with the misuse will be worse than the problems due to the lack of that particular trace flag.

-- Before a trace flag is documented, it must be thoroughly tested. FULLY. Across all SKUs, all security environments, all performance conditions. And that is not easy. A trace flag added for one particular situation or one rare bug is usually not tested to the full extent that documented features are. So it will stay undocumented.

-- Once a trace flag is added, it must be supported, into the future. And can’t be removed without officially deprecating it, at least one full version in advance. Some trace flags are known to have a limited potential usefulness, documenting them will force them to be maintained even when they’re no longer useful.

-- Because most of the undocumented trace flags are created for a limited set of circumstances, they are not tested in conjunction with other trace flags. In order to be fully tested, each trace flag would have to be tested in combination with every other trace flag. And if a particular combination of trace flags was not pretty, which of the trace flags involved should be left undocumented? It would be really hard to tell, even after spending all the time on all the testing.

The bar for Microsoft actually documenting a trace flag is therefore quite high. And sometimes if a feature introduced by trace flag is really needed, it will just become part of the product, or added using a different interface, such as a configuration option or database property.

But Microsoft does document new trace flags. If you look at the history of Books Online to see the number of documented trace flags, you can see the increase:

--  SQL Server 2005:               10 trace flags

-- SQL Server 2008:                13 trace flags

-- SQL Server 2008 R2:           15 trace flags

-- SQL Server 2012:                19 trace flags


So if you read about an undocumented trace flag, and you think it might be useful for you, be very careful when testing it. And if your careful testing shows it IS useful, let Microsoft know.

But if your testing shows it isn’t useful, or is actually harmful, you don’t need to let Microsoft know. They probably already know. After all, it’s undocumented.



Published Monday, June 3, 2013 9:33 AM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



kesehatan said:

July 4, 2018 9:56 PM

kesehatan said:

July 5, 2018 8:35 PM

kesehatan said:

July 6, 2018 9:02 PM

kesehatan said:

July 9, 2018 12:41 AM

obat kanker payudara terbaik said:

July 10, 2018 8:50 PM

obat keputihan berlebihan dan berbau said:

July 15, 2018 8:50 PM

obat diabetes said:

August 1, 2018 11:18 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:49 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:21 AM

obat benjolan di tubuh said:

August 30, 2018 7:40 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:46 PM

obat maag said:

September 17, 2018 7:10 PM

obat limfoma said:


September 23, 2018 6:50 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement