Trace Flags Report
Overview
A trace flag changes how SQL Server behaves, instance wide, usually because somebody once had a problem and this was the answer to it. The awkward part is what happens next: the person leaves, the version gets upgraded twice, and the flag is still on with nobody left who remembers why.
This report answers the three questions that matter about a flag that is already on.
- What does it do? The catalog covers around ninety flags.
- Is it still doing it? Several well known flags describe behaviour that later versions made the default. Trace flag 1117 is worth having on SQL Server 2014 and is doing nothing on 2016 or later.
- Will it still be on tomorrow? A flag set with
DBCC TRACEONis gone at the next restart. One written into the startup parameters as-Tcomes back. Nothing else in the product, and nothing in SQL Server Management Studio, puts those two side by side.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Trace Flags |
| Server Overview | The report launcher panel, under Configuration |
The page title reads Trace Flags for <server name>.
Requirements
- No minimum version.
DBCC TRACESTATUSworks everywhere, and the two extra reads each degrade to a stated “unknown” rather than turning the page away. VIEW SERVER STATEon the instance, for the startup parameter read. Without it the page still shows every flag and its verdict, and says plainly that it cannot tell you which ones survive a restart.
Nothing on this page is executed against the instance. DBCC TRACEOFF takes effect instance wide the moment it runs, and adding or removing a -T means editing startup parameters and bouncing the service. Both belong in somebody’s change window. The report writes the scripts and puts them on the clipboard.
The five bands
Every enabled flag lands in one of five bands, and the band depends on the version of SQL Server in front of it.
| Band | What it means |
|---|---|
| Act | Obsolete on this build, or actively risky. Trace flag 1211 lets a single statement take every lock in the instance. 902 and 3608 are recovery steps that leave the instance unsupported if they are left on. |
| Superseded | Did something real once, and the behaviour it asks for is already the default here. This is the band that needs the version. |
| Temporary | Diagnostic output. Correct to switch on while chasing something, wrong to find still on a year later. |
| Keep | Still earning its place on this build. |
| Unknown | Enabled here and not in the catalog. Not a failure state, because there are far more trace flags than Microsoft documents and support hands them out, but worth knowing about. |
Superseded is the reason the report reads the version. The old version of this page gave 1117 the same one line description on SQL Server 2012, where it is doing real work, as on SQL Server 2022, where tempdb has grown its files together without it since 2016. A description that is right everywhere and useful nowhere.
Nothing here is scored or totalled. A count of trace flags is not a health measure. An instance with six deliberate flags is in better shape than one with a single forgotten 1211, and any number that added them up would say the opposite.
Reading the comb
Each enabled flag is a hexagon, and they pack into rings around a hub. The closer to the middle, the sooner it wants your attention. Inside a ring the flags are in numeric order, because somebody looking for a specific flag is looking for a number.
The hub carries the build and the count. The build belongs there because it is what every verdict on the page was decided against, so a reader who disagrees with a band can see which version the report thinks it is looking at.
The mark on the bottom edge
| Mark | What it means |
|---|---|
| A solid bar | There is a -T for this flag in the startup parameters. It comes back after a restart. |
| A broken bar | It was set at runtime. It will be gone after the next restart. |
| No mark at all | The startup parameters could not be read, so the report will not guess. |
That last row matters. sys.dm_server_registry can come back empty on a locked down instance, and reporting that every flag is about to vanish on the strength of a query that failed would be the one failure mode that made this page actively misleading. So the marks disappear, the legend drops the entry, and the footer says why.
Hollow hexagons
A dashed, hollow hexagon marked off is a flag that a lot of instances run deliberately and this one does not have on. There are never more than a handful, and the list is version aware: 1117 and 1118 are only suggested on SQL Server 2014 and earlier, where they still do something.
They are labelled not enabled here, not “you should turn this on”. The recommendation text on each describes the trade and leaves the decision alone.
This is also what an instance with no trace flags at all gets: a hub reading 0 flags on and a ring of hollow suggestions, rather than the single line of text the old report had.
There is no scope mark
Deliberately. A session scoped trace flag is visible only to the session that set it, and the only session involved here is Database Health Monitor’s own connection, so scope is a dimension that cannot vary on this page. Drawing a mark for it on every hexagon would have been decoration. Scope is in the grid and the tooltip, where it costs nothing.
The toolbar
| Button | What it does |
|---|---|
| All | Every enabled flag. The default. |
| Act | Only the obsolete and the risky. |
| Superseded | Only the ones this version made redundant. |
| Temporary | Only the diagnostic ones. |
| Keep | Only the ones still doing useful work. |
| Unknown | Only the ones absent from the catalog. |
| Refresh | Re-read now. |
A band button with nothing behind it is disabled. All stays pressable.
The hollow suggestions are only drawn while All is selected. A reader who has filtered down to the Act band is asking a narrow question, and answering it with suggestions about flags that are off would be changing the subject.
Reading the grid
| Column | What it is |
|---|---|
| Flag | The trace flag number. |
| Verdict | The band, in words, with its colour swatch. |
| Persists | Startup parameter, Runtime only, or a dash where the startup parameters could not be read. |
| Scope | Global or Session. |
| Category | tempdb, locking, backup, optimizer, diagnostic, replication, memory, io, startup, security, availability or query store. |
| What it does | The catalog description. |
| Recommendation | Written against the running version, not against SQL Server in general. |
Selecting a row highlights the matching hexagon, and clicking a hexagon selects the row.
Right-click actions
| Item | What it gives you |
|---|---|
| Copy turn off script | DBCC TRACEOFF for this flag, commented out, plus the startup parameter half where the flag has one. |
| Copy turn on script | DBCC TRACEON and the -T line, both commented out. |
| Copy trace flag status script | The two reads behind this page, so you can check the report rather than take its word. |
| Read about trace flag N | Opens the Microsoft or Stedman Solutions page for it, where the catalog has one. |
The turn off script is always two halves when the flag persists. Running DBCC TRACEOFF and leaving the -T in place means the flag comes back at the next restart, which is exactly the mistake this page exists to stop somebody making.
Double-clicking a row or a hexagon opens the same detail in a window, with the band, the description, the scope, the persistence, the build and the recommendation above the scripts.
How to read the report
- Read the headline first. If it says some number of flags are gone at the next restart, that is the fact with an expiry date on it. Decide about those before the next reboot, not after.
- Work the middle ring outward. Act, then Superseded, then Temporary.
- Superseded flags are cleanup, not emergencies. They are not hurting anything. They are clutter in the startup parameters that will confuse whoever reads them next.
- Temporary flags are the interesting ones. A diagnostic flag still on months later usually means somebody switched it on during an incident and nobody switched it off.
- Chase the Unknown band. A flag not in the catalog is a flag somebody enabled on purpose, probably at the direction of Microsoft support. Find out which case it was.
Common patterns
Everything runtime only. Nobody has written any of these into the startup parameters. The next restart, planned or not, silently changes the configuration of the instance.
1117 and 1118 on SQL Server 2016 or later. Somebody carried a tempdb configuration forward from an older build. Harmless and worth removing so the next person does not have to work out whether they matter.
A single flag in Act and nothing else. Usually 1211 or a leftover from a recovery. Worth reading today.
No flags at all. The common case, and a good one. The hollow hexagons are suggestions worth a look, not a to-do list.
Where the data comes from
DBCC TRACESTATUS (-1)for which flags are on and their scope.SERVERPROPERTY('ProductVersion')for the build, which is what decides the Superseded band.sys.dm_server_registryfor the startup parameters, filtered to theSQLArgvalues under the instance’sParameterskey.sys.dm_os_sys_infofor the instance start time.
The product version is parsed out of ProductVersion rather than read from SERVERPROPERTY('ProductMajorVersion'), which does not exist before SQL Server 2014 SP2. Reading it there would return nothing on exactly the older instances where the version matters most, and a missing version quietly puts every superseded flag back into Keep.
Nothing is stored by this page.
Messages you may see
When no trace flags are enabled:
No trace flags are enabled on this instance.
When the startup parameters could not be read:
The startup parameters could not be read, so this page cannot say which flags survive a restart.
When the product version could not be read:
The product version could not be read, so no flag is reported as superseded.
When a refresh empties whatever the filter was sitting on:
No trace flags match the current filter. The All button has the rest.
Related reports
| Report | Why you would go there |
|---|---|
| Configuration Values | The sp_configure settings, which is where several trace flags have supported replacements. |
| Quick Scan Report | The broader configuration review. |
| Security Posture | Where flags like 3625 and 4616 belong in a hardening conversation. |
| Migration Planner | What a new server needs, including the flags worth carrying across and the ones worth leaving behind. |
Frequently asked questions
Why is a flag I rely on shown as Superseded? Because the behaviour it asks for is the default on this build. It is not doing harm, it is just no longer doing anything. The recommendation names the version that changed.
Does this report turn flags off for me? No. DBCC TRACEOFF takes effect instance wide the moment it runs. The report writes the script and you decide when.
Why does the Persists column show a dash? The startup parameters could not be read, usually a permissions problem. A dash means unknown, which is a different statement from Runtime only.
A flag is on and the report says Unknown. Is that bad? Not by itself. Microsoft documents a fraction of the trace flags that exist and support hands out others for specific cases. It is worth finding out which case this was.
Why is 4199 in Keep rather than Superseded? Because it still does something on current builds. The recommendation points at the QUERY_OPTIMIZER_HOTFIXES database scoped configuration, which is the safer place to make that decision, but the flag has not been made redundant the way 1117 has.
Why do session flags never appear? A session scoped flag is visible only to the session that set it. The only session this report has is its own connection, so a flag someone set in their own SSMS window is invisible here, and there is nothing the report can do about that.