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