SQL Server 2012: Trace Flags   6 comments


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 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
About these ads

Posted January 20, 2014 by Amit Banerjee in SQL Server

Tagged with ,

6 responses to “SQL Server 2012: Trace Flags

Subscribe to comments with RSS.

  1. FYI none of the KB, blog or whitepaper articles you listed are functioning URLs. Can you please correct this?

  2. Thanks for reporting the issue Rob. It was a copy/paste issue with Live Writer. I have fixed this now.

  3. Very useful and handy.

  4. 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

  5. This only applies during Buffer Pool rampup on standard edition. I have added this to the list as well.

  6. Very much useful

Add your comments here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,119 other followers

%d bloggers like this: