SQL Server 2005 Trace Flags


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.

Disclaimer
1. Data mentioned below is as of 29th June 2012.
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
210 Needed to enable fix for issue mentioned in KB Article. 945892 Y Y
212 Needed to enable fix for issue mentioned in KB Article. 951184 Y Y
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). BOL Link Y Y
274 Needed to enable fix for issue mentioned in KB Article. 949097 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
845 Enable lock pages support for Standard Edition 970070 Y N
1106 Enable tempdb allocation trace. Not suggested for production use. 947204 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
1807 Allow database files to be created on UNC path using CREATE DATABASE command 304261 Y Y
2301 Enable advanced decision support optimizations 920093 Y Y
2340 Disable batch sort to reduce CPU consumption Blog Y Y
2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. Article Y Y
2542 Skip mini-dump Blog 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
2558 Disables CHECKDB watson integration Blog 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
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
3628 Include other errors based on a severity. Blog Y Y
3629 Include messages marked to include with this trace flag enabled Blog Y Y
3656 Disables loading of dbghelp so the stack should not be put in the error log if this is enabled Blog 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
4101 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 318530 Y Y
4102 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 940128 Y Y
4103 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 919905 Y Y
4104 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 920346 Y Y
4105 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 920347 Y Y
4106 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 922438 Y Y
4107 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 923849 Y Y
4108 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 926024 Y Y
4109 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 926773 Y Y
4110 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 933724 Y Y
4111 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 934065 Y Y
4115 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 946793 Y Y
4116 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 950880 Y Y
4117 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 948445 Y Y
4119 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 942659 Y Y
4120 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 953948 Y Y
4121 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 942444 Y Y
4122 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 946020 Y Y
4124 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 948248 Y Y
4125 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 949854 Y Y
4126 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 959013 Y Y
4127 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 953569 Y Y
4128 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 955694 Y Y
4129 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 958547 Y Y
4131 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 956686 Y Y
4133 Needed to enable fix for issue mentioned in KB Article. Can be enabled by -T4199 also. 958006 Y Y
4136 Disable Parameter sniffing 980653 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
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
8026 Remove the dumptrigger setting after the first dump has been triggered. Blog 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
8033 Supress CPU drift warnings in the SQL Errorlog Blog Y Y
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
9082 Needed to enable fix for issue mentioned in KB Article 942906 Y Y
7300 Useful for troubleshooting Linked Server issues 280106 Y Y
Advertisements

10 thoughts on “SQL Server 2005 Trace Flags

  1. Pingback: Trace Flags – SQL Server 2005 / 2008 « Alex Souza

  2. Pingback: SQL Server 2008 Trace Flags « TroubleshootingSQL

  3. Pingback: SQL Server 2012: Trace Flags | TroubleshootingSQL

It is always good to hear from you! :)

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