This is something that I have been planning to release for some time but just have not been able to get to mainly due to procrastination! So this new year, I decided to get down to it! A list of all the publicly documented SQL Server 2012 Trace Flags.
The list below shows the trace flags available in SQL Server 2012 along with whether the trace flag can be enabled as a startup parameter or using DBCC TRACEON command or both.
SQL Server exposes multiple trace flags which are required to set specific server characteristics or to switch off a particular behavior. Some trace flags are required to enable the fix post the installation of the update.
This is currently a work-in-progress. The trace flags mentioned in the table below have the associated public article links mentioned which provide more information about the trace flag.
If you want a trace flag to be mentioned in the list below, then feel free to leave a comment. Note that only publicly documented trace flags will appear in this list.
The list of trace flags available for SQL Server 2005 are documented here.
The list of trace flags available for SQL Server 2008 are documented here.
Disclaimer
1. Data mentioned below is as of 14th January, 2014.
2. Trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.
3. Trace flags for hotfixes should be only enabled if the fix is applicable to the SQL Server instance that you are enabling the trace flag on.
Legend
Startup: If Y,Trace flag can be enabled by adding the trace flag number as a startup parameter using –T<trace flag number>
DBCC: If Y, Trace flag can be enabled using DBCC TRACEON(<trace flag number>,-1)
| Trace Flag | Description | KB | Startup | DBCC |
| 205 | Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog. | 195565 | Y | Y |
| 260 | Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). | BOL Link | Y | Y |
| 610 | Minimally logged inserts for indexed tables for fast load scenarios. Read article for limitations. | Article | Y | Y |
| 652 | Disable pre-fetch scans | 920093 | Y | Y |
| 661 | Disable ghost removal process | 920093 | Y | Y |
| 806 | Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag | 841776 | Y | Y |
| 815 | Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled | Article | Y | Y |
| 818 | Detect stale reads. Very helpful in analyzing recurring data corruption issues. | Article | Y | Y |
| 830 | Suppress stuck-stalled I/O warnings | Article | Y | Y |
| 834 | Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances. | 920093 | Y | N |
| 836 | Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server | 920093 | Y | N |
| 840 | On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts | 912322 | Y | Y |
| 1106 | Enable tempdb allocation trace. Not suggested for production use. | 947204 | Y | Y |
| 1117 | Even growth of all files in a file group | Whitepaper | Y | Y |
| 1118 | Prevents allocation of pages from mixed pages for all databases | 328551 | Y | Y |
| 1204 | Returns the resources and types of locks participating in a deadlock and also the current command affected. Useful for SQL Server 2000. | BOL Link | Y | Y |
| 1205 | Prints information about each deadlock search | 832524 | Y | Y |
| 1211 | Disabled lock escalation | BOL Link | Y | Y |
| 1222 | Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. To be used for SQL Server 2005 and above. | BOL Link | Y | Y |
| 1224 | Disables lock escalation based on the number of locks | BOL Link | Y | Y |
| 1260 | Disabled mini-dump for non-yield conditions | Whitepaper | Y | Y |
| 1262 | Dump everytime a non-yielding scheduler condition is detected. | Article | Y | Y |
| 1439 | Trace database restart and failover messages to SQL Errorlog for mirrored databases | 983500 | Y | Y |
| 1448 | Prevent replication latency with database mirroring enabled. Apply KB 983480 to prevent issues after enabling -T1448. | 937041 | Y | Y |
| 2301 | Enable advanced decision support optimizations | 920093 | Y | Y |
| 2340 | Disable batch sort to reduce CPU consumption | Blog | Y | Y |
| 2371 | Use a modified threshold for auto update statistics | 2754171 | Y | Y |
| 2389 | Automatically generated quick statistics for known ascending keys | 922063 | N | Y |
| 2390 | Automatically generated quick statistics for all columns that include known ascending keys or unknown ascending keys | 922063 | N | Y |
| 2528 | Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. | Article | Y | Y |
| 2544 | Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. | Article | Y | Y |
| 2546 | Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. | Article | Y | Y |
| 2551 | Generate a filtered dump of the SQL Server process. | Reference | Y | Y |
| 3004 | Write extended information about backup/restore to Errorlog | Blog | N | Y |
| 3014 | Write extended information about backup/restore to Errorlog | Blog | N | Y |
| 3023 | Automatically enable CHECKSUM for BACKUP command | 2656988 | N | Y |
| 3042 | Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size | Article | Y | Y |
| 3205 | Disable hardware compression for tape drives. | Article | N | Y |
| 3213 | Display backup/restore configuration parameters | Blog | N | Y |
| 3226 | Suppress successful backup messages in the logs | Article | N | Y |
| 3502 | Displays start and end of checkpoint. Can be very verbose and increase your transaction log size very quickly | 815436 | Y | Y |
| 3504 | Print Checkpoint summary | Blog | Y | Y |
| 3505 | Disable Checkpoint | 815436 | Y | Y |
| 3604 | Send trace output to client | Y | Y | |
| 3605 | Send trace output to SQL Errorlog | Y | Y | |
| 3608 | Prevents SQL Server from automatically starting and recovering any database except the master database | Article | Y | N |
| 3625 | Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages. | Article | Y | N |
| 3688 | Needed to enable fix for issue mentioned in KB Article. | 922578 | Y | N |
| 4010 | Allows only shared memory connections to the SQL Server | Blog | Y | Y |
| 4022 | Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0. | Article | Y | Y |
| 4029 | Push more verbose errors to the errorlog when read+write errors occur in the network layer on the server side. | Blog | Y | Y |
| 4199 | Controls multiple query optimizer changes previously made under multiple trace flags. Refer article for more details. | 974006 | Y | Y |
| 4606 | Disables password policy check during server startup | Blog | Y | Y |
| 4610 | Increases the size of the hash table that stores the cache entries by a factor of 8. | 959823 | Y | Y |
| 4616 | Makes server-level metadata visible to application roles | Article | Y | Y |
| 4618 | Limit the number of entries in the TokenAndPermUserStore store | 959823 | Y | Y |
| 4621 | Control TokenAndPermUserStore store size using registry value (Applies only to SQL Server 2005. For SQL Server 2008 and above, please refer 959823) | 959823 | Y | Y |
| 6527 | Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration | Article | Y | Y |
| 7806 | Enables a dedicated administrator connection (DAC) on SQL Server Express | Article | Y | N |
| 8002 | Treat affinity mask like processor affinity | 818765 | Y | Y |
| 8004 | Create memory dump for the first occurrence of out-of-memory condition | Article | Y | Y |
| 8011 | Disable the ring buffer for Resource Monitor | 920093 | Y | Y |
| 8012 | Disable the ring buffer for schedulers | 920093 | Y | Y |
| 8018 | Disable the exception ring buffer | 920093 | Y | N |
| 8019 | Disable stack collection for the exception ring buffer | 920093 | Y | N |
| 8020 | Disable working set monitoring | 920093 | Y | N |
| 8024 | Performs additional checks before generating non-yielding condition dump | Whitepaper | Y | Y |
| 8032 | Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger | Article | Y | N |
| 8048 | Forces CPU based partitioning | Blog | Y | N |
| 8602 | Ignore all index hints | Whitepaper | Y | Y |
| 8721 | Will dump information into the error log when AutoStat has been run. | 195565 | Y | Y |
| 8744 | Disable pre-fetching for ranges | 920093 | Y | Y |
TroubleshootingSQL 
FYI none of the KB, blog or whitepaper articles you listed are functioning URLs. Can you please correct this?
LikeLike
Thanks for reporting the issue Rob. It was a copy/paste issue with Live Writer. I have fixed this now.
LikeLike
Very useful and handy.
LikeLike
Very useful, thanks!
I’d suggest adding TF 840, which enables aggressive read-head scans on Standard Edition.
KB article: http://support.microsoft.com/kb/912322/en-us
Blog post with a description of the benefits: http://blogs.msdn.com/b/ialonso/archive/2011/12/09/the-read-ahead-that-doesn-t-count-as-read-ahead.aspx
LikeLike
This only applies during Buffer Pool rampup on standard edition. I have added this to the list as well.
LikeLike
Very much useful
LikeLike
Pingback: Trace Flags for SQL Server 2012 « LincolnBlog.net
Pingback: SQL Server – Trace Flags | Learning in the Open
Pingback: Trace Flags – SQL Server 2005 / 2008 – Alex Souza