[Update] – Added the Trace Flag 2453 that was added with SQL Server 2012 SP2
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: 2453 [Added June 2014]
Function: When trace flag 2453 is active, the optimizer can obtain an accurate picture of table cardinality after the table variable has been created.
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
2 thoughts on “SQL Server Trace Flag List – Updated”