Trace flags are required to modify Session and Server state very helpful when dealing with system Diagnostics and system maintenance. Trace flags are also dangerous so always think what do you need to enable or disable a specific trace flag.
Enabling and Disabling Trace flags can be done using multiple ways, SQL Server Configuration Manager, DBCC or Command line.
to enable trace flag using DBCC (my Favourite) use the following command
DBCC TRACEON (3604)
To Disable trace flag:
DBCC TRACEOFF (3604)
To Check for trace flag Status
There are 2 Different types of Trace Flags Documented , and None Documented
Below are a list of the most important ones
- Trace Flag 610
•Trace flag 610 controls minimally logged inserts into indexed tables
•Allows for high volume data loading
•Less information is written to the transaction log
•Transaction log file size can be greatly reduced
•Introduced in SQL Server 2008
•Data Loading Performance Guide white paper
- Trace Flag 834
•Trace flag 834 allows SQL Server 2005 to use large-page allocations for the memory that is allocated for the buffer pool.
•May prevent the server from starting if memory is fragmented and if large pages cannot be allocated
•Best suited for servers that are dedicated to SQL Server 2005
•Page size varies depending on the hardware platform
•Page size varies from 2 MB to 16 MB.
•Improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU
•Only applies to 64-bit architecture
- Trace Flag 835
•Trace flag 835 enables “Lock Pages in Memory” support for SQL Server Standard Edition
•Enables SQL Server to use AWE APIs for buffer pool allocation
•Avoids potential performance issues due to trimming working set
•SQL Server 2005 Service pack 3 Cumulative Update 4
•SQL Server 2008 Service Pack 1 Cumulative Update 2
•Only applies to 64-bit architecture
- Trace Flag 1211
•Trace flag 1211 disables lock escalation based on memory pressure or number of locks
•Database engine will not escalate row or page locks to table locks
•Scope: Global | Session
•Trace flag 1211 takes precedence over 1224
•Microsoft recommends using 1224
•Trace flag 1211 prevents escalation in every case, even under memory pressure
•Helps avoid "out-of-locks" errors when many locks are being used.
•Can generate excessive number of locks
•Can slow performance
•Cause 1204 errors
- Trace Flag 3226
•Trace flag 3226 prevents successful back operations from being logged
•By default SQL Server logs every successful backup operation to the ERRORLOG and the System event log
•Frequent backup operations can cause log files to grow and make finding other messages harder
- Trace Flag 4199 /* IMPORTANT */
•Trace flag 4199 enables all the fixes that were previously made for the query processor under many trace flags
•Any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag
•Except for fixes to bugs that can cause incorrect results or corruption
•Helps avoid unexpected changes to the execution plan
•Which means that virtually everyone is not necessarily running SQL Server with all the latest query processor fixes enabled
•Scope: Session | Global
•Consider enabling for “virgin” SQL Server deployments?
•Microsoft are strongly advising not to enable this trace flag unless you are affected
There is more you can find on MSDN site for more info but for me these the the most Important DOCUMENTED Trace flags
for undocumented trace flags I didn’t try any my self but here you can find some of what you are looking for
Trace Flag 3004
Most Database Administrators are aware of instant file initialization. In a nutshell, when instant file initialization is enabled the data files do not need to be zeroed out during creation. This can save an incredible amount of time during the restoration of VLDBs. As you can imagine, the zeroing out of a 1 TB data file can take a very long time.
Trace flag 3004 turns on information regarding instant file initialization. Enabling this trace flag will not make this information available to view. You will still need to turn on trace flag 3605 to send this information to the error log.
Trace Flag 3014
Trace flag 3014 provides detailed information regarding the steps performed during the backup and restore process. Normally, SQL Server only provides a limited amount of information in the error log regarding these processes. By enabling this trace flag you’ll be able to see some very detailed and interesting information.
Trace Flag 3604
Trace flag 3604 can be used under a variety of circumstances. If you’ve ever used DBCC IND or DBCC PAGE then you’ve probably already used trace flag 3604. It simply informs SQL Server to send some DBCC output information to the screen instead of the error log. In many cases, you have to use this trace flag to see any output at all.
Trace Flag 3605
Trace flag 3605 will send some DBCC output to the error log. This trace flag needs to be enabled to see the instant file initialization information made available by trace flag 3004