SQL Server Trace Flag List

[Updated – See this link for a more recent version of this page. 6/24/2014]

I recently saw an article with a list of known SQL Server Trace Flags, and thought I would help communicate the list. Trace Flags are additional settings that in some way alters the default behavior of SQL Server. You can use these in a couple of different ways:

• You can use the DBCC TRACEON and DBCC TRACEOFF commands
• You can use the -T option in the startup configuration fo the SQL Server Service

You can use the DBCC TRACESTATUS command to check which flags are enabled, if any.

Trace Flags:

Trace Flag : 101
Function: Verbose Merge Replication logging output for troubleshooting Merger repl performance

Trace Flag : 102
Function: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance

Trace Flag : 105
Function: Join more than 16 tables in SQL server 6.5

Trace Flag : 106
Function: This enables you to see the messages that are sent to and from the Publisher, if you are using Web Synchronization

Trace Flag : 107
Function: Alter input rules for decimal numbers

Trace Flag : 168
Function: Bugfix in ORDER BY

Trace Flag : 205
Function: Log usage of AutoStat/Auto Update Statistics

Trace Flag : 253
Function: Prevent adhoc query plans from staying in cache (SQL 2005)

Trace Flag : 260
Function: Prints Extended stord proc DLL versioning info

Trace Flag : 272
Function: Grenerates a log record per identity increment. Can be used to convert SQL 2012 back to old style Identity behavior

Trace Flag : 302
Function: Output Index Selection info

Trace Flag : 310
Function: Outputs info about actual join order

Trace Flag : 323
Function: Outputs detailed info about updates

Trace Flag : 345
Function: Changes join order selection logic in SQL Server 6.5

Trace Flag : 610
Function: Minimally logged inserts to indexed tables

Trace Flag : 652
Function: Disable page pre-fetching scans

Trace Flag : 661
Function:  Disable the ghost record removal process

Trace Flag : 806
Function:  Turn on Page Audit functionality, to verify page validity

Trace Flag : 818
Function:  Turn on ringbuffer to store info about IO write operations. Used to troubleshoot IO problems

Trace Flag : 834
Function: Large Page Allocations

Trace Flag : 836 
Function: Use the max server memory option for the buffer pool

Trace Flag : 845
Function: Enable Lock pages in Memory on Standard Edition

Trace Flag : 902
Function: Bypass Upgrade Scripts

Trace Flag : 1117
Function: Simultaneous Autogrowth in Multiple-file database

Trace Flag : 1118
Function: Force Uniform Extent Allocation

Trace Flag : 1119
Function: Turns of mixed extent allocation (Similar to 1118?)

Trace Flag : 1140
Function: Fix for growing tempdb in special cases

Trace Flag : 1200
Function: Prints detailed lock information

Trace Flag : 1124
Function: Unknown. Has been reportedly found turned on in some SQL Server instances running Dynamics AX. Also rumored to be invalid in public builds of SQL Server

Trace Flag : 1204
Function: Returns info about deadlocks

Trace Flag : 1211
Function: Disables Lock escalation caused by mem pressure

Trace Flag : 1222
Function: Returns Deadlock info in XML format

Trace Flag : 1224
Function: Disables lock escalation based on number of locks

Trace Flag : 1236
Function: Fixes performance problem in scenarios with high lock activity in SQL 2012 and SQL 2014

Trace Flag : 1264
Function: Collect process names in non-yielding scenario memory dumps

Trace Flag : 1448
Function: Alters replication logreader functionality

Trace Flag : 1462
Function: Disable Mirroring Log compression

Trace Flag : 1717
Function: MSShipped bit will be set automatically at Create time when creating stored procedures

Trace Flag : 1806
Function: Disable Instant File Initialization

Trace Flag : 1807
Function: Enable option to have database files on SMB share for SQL Server 2008 and 2008R2

Trace Flag : 2301
Function: Enable advanced decision support optimizations

Trace Flag : 2312
Function: Forces the query optimizer to use the SQL Server 2014 version of the cardinality estimator when creating the query plan when running SQL Server 2014 with database compatibility level 110

Trace Flag : 2335
Function: Generates Query Plans optimized for less memory

Trace Flag : 2340
Function: Disable specific SORT optimization in Query Plan

Trace Flag : 2371
Function: Change threshold for auto update stats

Trace Flag : 2372
Function: Displays memory utilization during the optimization process

Trace Flag : 2373
Function: Displays memory utilization during the optimization process

Trace Flag : 2389
Function: Enable auto-quick-statistics update for known ascending keys

Trace Flag : 2390
Function: Enable auto-quick-statistics update for all columns

Trace Flag : 2430
Function: Fixes performance problem when using large numbers of locks

Trace Flag : 2470
Function: Fixes performance problem when using AFTER triggers on partitioned tables

Trace Flag : 2528
Function: Disables parallellism in CHECKDB etc.

Trace Flag : 2537
Function: Allows you to see inactive records in transactionlog using fn_dblog

Trace Flag : 2540
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2541
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2542
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2543
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2544
Function: Produces a full memory dump

Trace Flag : 2545
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2546
Function: Dumps all threads for SQL Server in the dump file

Trace Flag : 2547
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2548
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2549
Function: Faster CHECKDB

Trace Flag : 2550
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2551
Function: Produces a filtered memory dump

Trace Flag : 2552
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2553
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2554
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2555
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2556
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2557
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2558
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2559
Function: Unknown, but related to controlling the contents of a memorydump

Trace Flag : 2562
Function: Faster CHECKDB

Trace Flag : 2588
Function: Get more information about undocumented DBCC commands

Trace Flag : 2861
Function: Keep zero cost plans in cache

Trace Flag : 3004
Function: Returns more info about Instant File Initialization

Trace Flag : 3014
Function: Returns more info about backups to the errorlog

Trace Flag : 3042
Function: Alters backup compression functionality

Trace Flag : 3101
Function: Fix performance problems when restoring database with CDC

Trace Flag : 3205
Function: Disable HW compression for backup to tape drives

Trace Flag : 3213
Function: Output buffer info for backups to ERRORLOG

Trace Flag : 3226
Function: Turns off “Backup Successful” messages in errorlog

Trace Flag : 3422
Function: Log record auditing

Trace Flag : 3502
Function: Writes info about checkpoints to teh errorlog

Trace Flag : 3505
Function: Disables automatic checkpointing

Trace Flag : 3604
Function: Redirect DBCC command output to query window

Trace Flag : 3605
Function: Output buffer info for backups to ERRORLOG

Trace Flag : 3607
Function: Skip recovery on startup

Trace Flag : 3608
Function: Recover only Master db at startup

Trace Flag : 3609
Function: Do not create tempdb at startup

Trace Flag : 3625
Function: Masks some errormessages

Trace Flag : 3656
Function: Enables resolve of all callstacks in extended events

Trace Flag : 3659
Function:  Enables logging all errors to errorlog during server startup

Trace Flag : 3688
Function: Removes messages to errorlog about traces started and stopped

Trace Flag : 3801
Function: Prohibits use of USE DB statement

Trace Flag : 4013
Function: Log each new connection the errorlog

Trace Flag : 4022
Function: Bypass Startup stored procedures

Trace Flag : 4130
Function: XML performance fix

Trace Flag : 4134
Function: Bugfix for error: parallell query returning different results every time

Trace Flag : 4135
Function: Bugfix for error inserting to temp table

Trace Flag : 4136
Function: Parameter Sniffing behaviour alteration

Trace Flag : 4137
Function: Fix for bad performance in queries with several AND criteria

Trace Flag : 4138
Function: Fixes performance probles with certain queries that use TOP statement

Trace Flag : 4199
Function: Turn on all optimizations

Trace Flag : 4606
Function: Ignore domain policy about weak password

Trace Flag : 4616
Function: Alters server-level metadata visibility

Trace Flag : 6527
Function: Alters mem dump functionality

Trace Flag : 7300
Function: Outputs extra info about linked server errors

Trace Flag : 7502
Function: Disable cursor plan caching for extended stored procedures

Trace Flag : 7806
Function: Enables DAC on SQL Server Express

Trace Flag : 7826
Function: Disable Connectivity ringbuffer

Trace Flag : 7827
Function: Record connection closure info in ring buffer

Trace Flag : 8002
Function: Changes CPU Affinity behaviour

Trace Flag : 8010
Function: Fixes problem that SQL Server services can not be stopped

Trace Flag : 8011
Function: Disable the ring buffer for Resource Monitor

Trace Flag : 8012
Function: Disable the ring buffer for schedulers

Trace Flag : 8015
Function: Ignore NUMA functionality

Trace Flag : 8018
Function: Disable the exception ring buffer

Trace Flag : 8019
Function: Disable stack collection for the exception ring buffer

Trace Flag : 8020
Function: Disable working set monitoring

Trace Flag : 8026
Function: SQL Server will clear a dumptrigger after generating the dump once

Trace Flag : 8030
Function: Fix for performance bug

Trace Flag : 8032
Function: Alters cache limit settings

Trace Flag : 8038
Function: will drastically reduce the number of context switches when running SQL 2005 or 2008

Trace Flag : 8040
Function: Disables Resource Govenor

Trace Flag : 8048
Function: NUMA CPU based partitioning (Related to: 8015, 9024)

Trace Flag : 8207
Function: Alters Transactional Replication behaviour of UPDATE statement

Trace Flag : 8209
Function: Output extra infomation to errorlog regarding replication of schema changes in SQL Server Replication

Trace Flag : 8602
Function: Disable Query Hints

Trace Flag : 8605
Function: Displays logical and physical trees used during the optimization process

Trace Flag : 8607
Function: Displays the optimization output tree during the optimization process

Trace Flag : 8649
Function: Set Cost Threshold for parallelism to 0

Trace Flag : 8675
Function: Displays the query optimization phases for a specific optimization

Trace Flag : 8722
Function: Disable all hints exept locking hints

Trace Flag : 8744
Function: Disable pre-fetching for ranges

Trace Flag : 8755
Function: Disable all locking hints

Trace Flag : 8757
Function: Skip trivial plan optimization and force a full optimization

Trace Flag : 8780
Function: Give the optimizer more time to find a better plan

Trace Flag : 9024
Function: Performance fix for AlwaysON log replication (Related to: 8048)

Trace Flag : 9481
Function: Forces the query optimizer to use the SQL Server 2012 version of the cardinality estimator when creating the query plan when running SQL Server 2014 with the default database compatibility level 120

Trace Flag : 9485
Function: Disables SELECT permission for DBCC SHOW_STATISTICS.

Trace Flag : 9806
Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM

Trace Flag : 9807
Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM

Trace Flag : 9808
Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM