What trace flags are (quickly)

Trace flags are switches that alter SQL Server engine behavior. Some are documented/supported by Microsoft; many are undocumented (internal, historical, or fix-gated). Use them carefully, test in lower environments first, and document who turned them on, why, when, and how to roll back.

How to see / enable / disable

-- What is on now (session + global)
DBCC TRACESTATUS(-1);

-- Turn on/off globally (until restart)
DBCC TRACEON(<flag>, -1);
DBCC TRACEOFF(<flag>, -1);

-- Verify a specific flag
DBCC TRACESTATUS(<flag>);

-- Startup (persistent)
-- Windows: add -T<flag> in SQL Server Configuration Manager, restart
-- Linux: sudo /opt/mssql/bin/mssql-conf set traceflag <flag> on && sudo systemctl restart mssql-server

-- Per-query (optimizer-related):
SELECT ... OPTION (QUERYTRACEON <flag>);

Prefer Database-Scoped Configuration and USE HINT() when possible—they’re safer and discoverable.

---

Version notes that matter

• SQL Server 2016+: A number of “classic” flags became default behavior or were superseded (notably 1117 / 1118 for TempDB; 2371 behavior for large-table stats with compat level ≥130).
• Optimizer hotfix gating changed: 4199 behavior evolved in 2016+; many fixes tie to compatibility level and QUERY_OPTIMIZER_HOTFIXES.
• AG Parallel Redo arrived and improved across 2016→2019→2022; disabling (3459) is now an edge-case workaround, not a default practice.

---

Big List — grouped by category

(Flags shown in bold; brief description; version applicability in italics when relevant. “Undocumented” = community-observed / KB-referenced but not formally supported. See references.)

A. Engine / Logging / Diagnostics

• -1 — Apply TF globally with DBCC TRACEON/TRACEOFF (special DBCC semantic). All versions. SQLServerCentral
• 205 — Log recompiles due to auto-update stats (error log chatter). Old; use sparingly. SQLServerCentral
• 260 — Extended proc DLL versioning message to error log. Legacy, documented behavior snippet. SQLServerCentral
• 3604 — Send DBCC outputs to client; 3605 — send DBCC outputs to error log. All versions. (Docs/canonical usage.) Microsoft Learn
• 3226 — Suppress “backup successful” messages from error log (failed backups still log). Commonly enabled on all supported versions. Glenn’s SQL PerformanceSQL Shack
• 3004, 3014 — Extra backup/restore diagnostics; 3023 — BACKUP CHECKSUM defaulting (varies by version, prefer explicit BACKUP options). Use for targeted backup investigations. SQLServerCentral
• 445 (+3605) — Compile issued message in errorlog (undocumented/noisy). Lab only. SQLServerCentral

B. Optimizer / Cardinality Estimation (CE) / Parameter sniffing

• 4199 — Enables plan-affecting optimizer hotfixes not already tied to your compat level; in 2016+ many fixes come with compat level; QUERY_OPTIMIZER_HOTFIXES is preferred. Use after testing. Microsoft Learn
• 9481 — Force legacy CE for testing; 2312 — force new CE for testing. Prefer DB-scoped config LEGACY_CARDINALITY_ESTIMATION or USE HINT in 2016 SP1+. Microsoft Learn
• 4136 — Disable parameter sniffing (legacy approach). Prefer DB-scoped PARAMETER_SNIFFING = OFF in modern versions. (Doc guidance via DSC.) Microsoft Learn
• 174 — Increase plan cache buckets on x64 (contention mitigation for ad-hoc plans—test carefully). Fix context in KB. SQLServerCentral
• 326, 330, 342, 320, 310, 325 — Various undocumented plan/heuristic/sort costing debug outputs (ancient; lab only). Historical/training use. SQLServerCentral

C. Statistics / Auto-update thresholds

• 2371 — Dynamic stats update thresholds for large tables. Behavior became default with compat level ≥130 on SQL 2016+; TF relevant mainly pre-2016. Microsoft Learn

D. TempDB / Allocation / Concurrency

• 1117 — Grow all files in a filegroup evenly; 1118 — reduce mixed extent contention. Obsolete in SQL Server 2016+; replaced by ALTER DATABASE options; not needed for TempDB in modern versions. TECHCOMMUNITY.MICROSOFT.COMSQL ShackBrent Ozar Unlimited®

E. DBCC / Consistency checking (large DBs)

• 2549 — Treat each data file as on separate volume for CHECKDB checks (speeds metadata enumeration). Not needed in SQL 2016+.
• 2562 — Reduce temp space / improve throughput for CHECKDB. May still help depending on workload/layout even in newer versions.
• 2566 — Skip certain page checks to reduce overhead (safe, targeted). Use only during CHECK runs, then disable.* SQL Sasquatch

F. Replication (Merge / Agents)

• 101, 102 — Verbose Merge Replication logging (msmerge_history, etc.). Global; for troubleshooting. SQLServerCentral
• 106 — Web Sync extra logging (Replication Merge Agent, XML in/out files). Legacy doc. SQLServerCentral
• 120, 210, 212 — Legacy fixes/perf changes around replication/cursor behavior (undocumented/historic). Use only if matching a specific KB symptom. SQLServerCentral

G. Availability Groups (AG) / Mirroring / Redo / Compression / Seeding

• 3459 — Disable Parallel Redo on secondary replicas (forces serial redo). Use only to work around parallel redo issues (e.g., assertion/crash on older CUs, extreme PARALLEL_REDO_* waits). Restart may be needed to re-enable parallel redo after disabling; newer builds improve this significantly. Microsoft Support+1SQLSkills
• 1462 — Disable log stream compression for asynchronous replicas (default is enabled for async; this turns it off for testing/baseline). Global only. Microsoft Learn+1
• 9592 — Enable log stream compression for synchronous replicas (default is disabled for sync; this turns it on). Useful for bandwidth-constrained sync links; measure CPU impact. SolarWinds THWACK CommunitySQLpassionMicrosoft Press Store
• 9567 — Enable compression during Automatic Seeding (default is disabled to save CPU). Helps reduce seeding time at the cost of CPU. Microsoft LearnMicrosoft Press Store
• (9591 shows up in community lists alongside 9592 as related to AG compression toggles; treat as undocumented, verify per build.) SQLServerCentral

Notes on versions
– Parallel Redo shipped in SQL Server 2016; behavior and scalability improved in 2017/2019 and again in SQL Server 2022 (thread pool, batching, improved scheduling). Consider upgrading rather than living with 3459 long-term. SQLSkills

H. Identity / String / Legacy behavior toggles (selected)

• 272 — Disable identity value caching (avoid gaps across restarts/failovers) — relevant to SQL 2012/2014 era; alternatives exist in newer versions. Global; use only if gap sensitivity matters. SQLServerCentral
• 242, 243, 244, 246 — Old compatibility toggles for nullability / correlated subqueries / interim constraint checks (undocumented). Historical. SQLServerCentral
• 262 — Preserve trailing spaces in CASE literal strings (SQL 7 behavior) (undocumented). Historical. SQLServerCentral

I. Deadlocks / Locking diagnostics

• 1204 — Deadlock details by node to error log.
• 1222 — Deadlock details by process/resource to error log.
• 1200 — Detailed lock requests and timeouts (verbose; session use in labs). Use 1204/1222 temporarily in prod. Microsoft Learn

J. Columnstore / Batch / In-Memory (selected, version-specific)

• 6532, 6533 — Columnstore spatial/query perf (2012/2014-era specifics; test only). Community-documented. SQLServerCentral
• 9347 — Disable batch mode sort (testing/triage). Undocumented; lab only. SQLServerCentral
• 9830 — Hekaton native compile tracing (investigation). Undocumented; lab only. SQLServerCentral

K. Query Store / Flush/Load behavior

• 7745, 7752 — Control Query Store flush/load (niche troubleshooting). Community-documented. SQLServerCentral

---

Practical playbooks (ready to paste)

Deadlock capture (temporary)

DBCC TRACEON(1204, -1);
DBCC TRACEON(1222, -1);
-- reproduce issue, then:
EXEC sys.sp_readerrorlog;
DBCC TRACEOFF(1204, -1);
DBCC TRACEOFF(1222, -1);

Microsoft Learn

Quiet backup “success” spam (persistent)

• Add -T3226 at startup or:

DBCC TRACEON(3226, -1);

Glenn’s SQL Performance

CE regression triage (targeted)

-- Force legacy CE for one repro query
SELECT ... OPTION (QUERYTRACEON 9481);

-- Safer global alternative:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Microsoft Learn

AG parallel redo bottleneck triage (edge case)

-- Temporarily force SERIAL redo to test if parallel redo is the bottleneck
DBCC TRACEON(3459, -1);

-- If you later TRACEOFF 3459 and parallel redo doesn't resume,
-- restart the instance on affected builds (per KB).

Microsoft Support+1

AG log compression behavior controls

-- Async replicas: disable compression (default is ON for async)
DBCC TRACEON(1462, -1);

-- Sync replicas: enable compression (default is OFF for sync)
DBCC TRACEON(9592, -1);

-- Automatic seeding: enable compression (default is OFF)
DBCC TRACEON(9567, -1);

Microsoft Learn+1Microsoft Press Store

---

Version cheat-sheet (selected highlights)

• TempDB flags 1117/1118 → not needed from SQL Server 2016 onward (use ALTER DATABASE options; TempDB defaults improved). TECHCOMMUNITY.MICROSOFT.COM
• Stats 2371 → behavior is default with compat level ≥130 (SQL 2016+). Microsoft Learn
• Optimizer hotfixes 4199 → in 2016+, many fixes come via compat level; the TF gates future hotfixes at that level; consider QUERY_OPTIMIZER_HOTFIXES. Microsoft Learn
• CHECKDB flags 2549/2562/2566 → 2549 no longer needed in 2016+; 2562 may still help; 2566 is situational. SQL Sasquatch
• AG parallel redo → 2016 debut, improved in later versions and 2022 (thread pool, batching). Prefer upgrading over long-term TF 3459 usage. SQLSkills

Appendix: SQL Server Trace Flags – Complete Reference Table

Flag	Description	Scope	Version / Notes	Source
101, 102	Verbose Merge Replication logging	Global	Legacy, all versions	SQLServerCentral
106	Replication Web Sync XML logging	Global	Legacy	SQLServerCentral
174	Increase plan cache buckets (ad-hoc perf)	Global	Older versions; test carefully	Microsoft KB, SSC
205	Log recompiles due to auto-update stats	Global	Old (pre-2012 mainly)	SSC
242–246	Legacy nullability / constraint checks	Global	SQL 6.5/7 era	SSC
260	DLL version info to error log	Global	Legacy	SSC
272	Disable identity cache (avoid gaps)	Global	SQL 2012/2014, later replaced by ALTER SEQUENCE	Microsoft Docs
326, 330, 342, 320, 310, 325	Optimizer costing / debug outputs	Session/Global	Undocumented, lab only	SSC
445 + 3605	Compiler message to errorlog	Global	Undocumented	SSC
652, 653	Disable read-ahead (global/session)	Session/Global	Rarely helps; test only	SSC
1117	Grow all files in filegroup evenly	Global	Obsolete since 2016 (default behavior)	TechCommunity
1118	Reduce mixed extent contention	Global	Obsolete since 2016	TechCommunity
1200	Detailed lock activity to error log	Session/Global	Diagnostic only	Docs
1204	Deadlock details by node	Global	Use temporarily	Docs
1222	Deadlock details by process/resource	Global	Use temporarily	Docs
1462	Disable log stream compression for async AG replicas	Global	All supported	Microsoft Docs
2312	Force new CE	Query/DB	Pre-2016: TF; 2016+ use DB-scoped config	Docs
2371	Dynamic auto-update stats thresholds	Global	Default in compat ≥130 (2016+)	Docs
2549	Treat each file as separate volume (CHECKDB)	Global	Not needed since 2016	SQLPerformance, SSC
2562	CHECKDB performance improvements	Global	Still relevant depending on workload	SQLPerformance
2566	CHECKDB skip certain page checks	Global	Optional	SSC
272	Disable identity cache (again, identity gaps)	Global	SQL 2012/2014 era	Docs
3226	Suppress backup success messages	Global	All versions; safe default	Docs
3459	Disable parallel redo on AG secondary	Global	2016+; workaround for redo bugs	Microsoft KB
3604	DBCC output to client	Session	All versions	Docs
3605	DBCC output to error log	Global	All versions	Docs
4199	Enable optimizer hotfixes	Global/Query	All; in 2016+ tied to compat level	Docs
460	Change truncation error ID	Global	2016 SP2 CU6, 2017 CU12+	Docs
6532, 6533	Columnstore / spatial perf tweaks	Global	SQL 2012/2014	SSC
7745, 7752	Query Store flush/load behavior	Global	SQL 2016+	SSC
7806	DAC enabled for Express	Global	All versions	SSC
8207	Query plan feedback fix (optimizer)	Global	Newer versions	Docs
9347	Disable batch mode sort	Query	2016+; undocumented	SSC
9389	CE behavior for ascending key	Global	Modern builds	Docs
9567	Enable AG automatic seeding compression	Global	2016+	Docs
9592	Enable AG sync log compression	Global	2016+	Docs
9591	Related to AG log compression (undocumented)	Global	Use cautiously	SSC
9830	Hekaton native compile tracing	Global	2014+	SSC
10316	Query optimizer fix (various)	Global	2017+	Docs
11024	Memory grant feedback tuning	Global	2019+	Docs

Summary (short and honest)

• You now have a broad, version-aware list of commonly used and AG-specific trace flags, with descriptions and usage notes.
• Where Microsoft made features the default (2016+), I’ve called that out so you don’t carry legacy flags forward.
• For undocumented flags, the rule is simple: lab only unless you’re following a Microsoft Support directive.
• Always test, monitor, and document.

---

References (primary sources)

• Microsoft Docs — DBCC TRACEON/TRACEOFF/TRACESTATUS reference and specific TF notes (e.g., 1462 entry and scope). Microsoft Learn
• Microsoft Docs — Tune compression for Availability Group (AG TF 1462, 9567, 9592 behaviors). Microsoft Learn
• Microsoft Troubleshooting (Support KB) — Parallel Redo issues & TF 3459 workaround; behavior when toggling back. Microsoft Support+1
• Microsoft TechCommunity — SQL 2016 changes: 1117/1118 superseded by ALTER DATABASE options; TempDB/user DB behavior. TECHCOMMUNITY.MICROSOFT.COM
• Microsoft “Recommended updates & configuration options” — versioned guidance; note that many older flags became defaults in 2016+. Microsoft Learn
• Glenn Berry on TF 3226 (clear explanation and when to use). Glenn’s SQL Performance
• SQL-Sasquatch (CHECKDB flags in 2016++), and community deep dives on redo waits. SQL SasquatchSQLSkills
• SQLServerCentral — Complete list (593) used to cross-check and fill in historical/undocumented entries (treat as community catalog). SQLServerCentral
• Microsoft Docs: DBCC TRACEON/TRACEOFF/TRACESTATUS reference
• Microsoft KB articles: e.g., Parallel Redo crash fix, TF 3459
• Glenn Berry & Brent Ozar blogs on 3226, optimizer hotfixes, CE flags.
• SQLPerformance.com & SQLSasquatch: CHECKDB performance flags 2549/2562/2566.
• SQLServerCentral: Trace Flags – Complete List (593 entries).
• MVP blogs (Erin Stellato, Paul Randal, Paul White, Bob Ward): commentary on optimizer flags, redo, stats, etc.

Download the complete trace flag catalog .