Trace Flags in SQL Server are used to turn ON or OFF particular behavior. TraceFlags are used for variety of purposes like Logging extended information in to SQL Server errorlog, Troubleshooting or improving SQL Server Performance, Enabling certain features or functionality, etc.
Below are the SQL Server Trace Flags from Trace Flag 902 to Trace Flag 1462
SQL Server Trace Flag 902 – SQL server may not start after install or uninstall of service pack or Cumulative Update patches. Trace Flag 902 can be used to start SQL Server by skippling applying the upgrade scripts.
SQL Server Trace Flag 1106 – In SQL Server 2005, Used space in tempdb increases continuously when you run a query that creates internal objects in tempdb. KB 947204
SQL Server Trace Flag 1117 – Even growth of all files in a file group. Grows all data files at once, else it goes in turns.
SQL Server Trace Flag 1118 – Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent. There is now a cache of temp tables. When a new temp table is created on a cold system it uses the same mechanism as for SQL 8. When it is dropped though, instead of all the pages being deallocated completely, one IAM page & one data page are left allocated, then the temp table is put into a special cache. Subsequent temp table creations will look in the cache to see if they can just grab a pre-created temp table. If so, this avoids accessing the allocation bitmaps completely. The temp table cache isn’t huge (32 tables), but this can still lead to a big drop in latch contention in tempdb.
SQL Server Trace Flag 1119 – Turns off mixed extent allocation.
SQL Server Trace Flag 1140 – Continuous tempdb growth after upgrading SQL 2005 pre-SP2 to SP3/SP4 or to SQL 2008 or newer. KB 2000471.
SQL Server Trace Flag 1200 – Prints detailed lock information as every request for a lock is made and consists of the process ID and type of lock requested.
SQL Server Trace Flag 1202 – Insert blocked lock requests into syslocks.
SQL Server Trace Flag 1204 – Returns resources and types of locks participating in a deadlock and command affected.
SQL Server Trace Flag 1205 – Prints information about each deadlock search
SQL Server Trace Flag 1206 – Used to complement flag 1204 by displaying other locks held by deadlock parties.
SQL Server Trace Flag 1211 – Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory.
If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.
SQL Server Trace Flag 1222 – Returns the resources & 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.
SQL Server Trace Flag 1224 – Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation (when > 40%). The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:
40% of the memory that is used by Db Engine, exclusive of memory allocation using AWE. This is applicable when the locks parameter of sp_configure is set to 0.
Forty percent of the lock memory that is configured by using the locks parameter of sp_configure.
If both trace flag 1211 & 1224 are set, 1211 takes precedence. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, it’s recommend to use 1224 which helps avoid “out-of-locks” errors when many locks are being used.
SQL Server Trace Flag 1260 – Disabled mini-dump for non-yield conditions.
SQL Server Trace Flag 1262 – Dump everytime a non-yielding scheduler condition is detected.
SQL Server Trace Flag 1400 – In SQL Server 2005 RTM, Enables creation of database mirroring endpoint, which is required for setting up and using database mirroring.
SQL Server Trace Flag 1439 – Trace database restart and failover messages to SQL Errorlog for mirrored databases
SQL Server Trace Flag 1448 – Prevent replication latency with database mirroring enabled. When the principal database is running exposed or is isolated the Log Reader Agent will waits for log records to harden on the mirror before replicating them to the Distributor. When publisher is started with trace flag 1448, the Log Reader Agent can continue replicating changes regardless of the mirroring state.
SQL Server Trace Flag 1449 – When you use SNAC to connect to an instance of a principal server in a database mirroring session: “The connection attempted to fail over to a server that does not have a failover partner”. KB 936179
SQL Server Trace Flag 1462 – In SQL Server 2008, Turns off log stream compression and effectively reverts the behavior back to version 2005.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.