Trace Flags in SQL Server are used for either setting up a server characteristic or for switching off a server behavior. They help generate information about internal activities of the server, which primarily helps the developers of SQL server to troubleshoot their codes.
Nevertheless, it is also necessary to ensure securing users from unknowingly changing their system behavior in undesired ways. Thus, Microsoft has not permitted the documentation of all the trace flags that exist and advises avoiding their use. Not all trace flags are to be avoided, some do come in great use. Nevertheless, at the same time, it is important that you understand how to use a certain trace flag in SQL server and in what condition. Some trace flags are to be used from one connection at the time of testing while some should only be used when they are enabled across the server.
Different SQL Server Trace Flags and Their Usage
Trace flags are available in an innumerous number, however, no official or unofficial documentation exists listing them all. There are four ways of enabling trace flags out of which, only one can be used for turning on a single SQL Server connection.
Only administrators are authorized to use a trace flag. Therefore, it might seem harder than expected to enable a trace flag in SQL server for one connection and preventing rest of the connections from being affected by it.
Uses of Trace Flags
Supposedly, if trace flag 3205 has been enabled while the SQL Server instance initiates hardware compression will automatically be disabled for tape drivers. The standard use of trace flags is to examine the performance issues of SQL Server or for debugging the stored procedures.
IMPORTANT: It is possible to enable trace flags of Global Level with DBCC TRACEON or the startup parameter ‘-t’. DBCC TRACEON activates session-level trace flags while QUERYTRACEON activates query-level trace flags.
There is an innumerous count of classified as well as non-classified trace flags. Here are the top three Trace Flags in SQL Server that are important for a DBA.
An Outline of the Top 3 Essential Trace Flags
3226 Flag: Using this trace flag enables the elimination of successful backup messages from being listed in the Errorlog.
Usage: 3226 is a must-have trace flag. There is nothing more frustrating than having to go through thousands of lines in an Errorlog just to find messages stating that the backups are successful. That is not typically the job of an Errorlog, its job is to notify about the backup NOT working. Therefore, this is a highly advised trace flag for use by DBAs to maintain a clean Errorlog.
3213 Flag: 3213 trace flag generates the internal configuration of buffers along with the memory size, which will be used by SQL while performing a backup, and restore task. You will have to combine the use of this trace flag along with the trace flag 3604/05.
NOTE: Trace Flags 3604 and 3605 are meant to generate tracing activity in SQL Server either in the output of current session or in the Errorlog respectively.
Usage: Use of the 3213 trace flag in SQL server is best when you are trying to perform tuning for faster backup & restore. The buffer size is configurable and differences in the sizes lead to bigger impact put on the backup / restore operations. The flag can be used for checking system configuration and get the best of it.
1118 Flag: Trace flag 1118 is meant to eliminate the allocation of various assorted extents on a single instance and perform uniform extents on new objects.
Usage: Using the 1118 trace flag is very important. Generally the flag is correlated with the tempdb contention whereas, if turned on the trace flag changes all user DB’s behavior. User database do not adversely affect tables every time as TEMPDB does. Therefore, the effect does not make much of an impact. In the latest SQL versions, optimizations are already done to prevent allocation contentment however; it is still recommended that this feature be turned on.
Trace flags in SQL Server are a classified concept that is generally not recommended by Microsoft for usage. However, even if one does make use of them it is necessary to understand for what purpose are they used and what will be the consequences. These flags are useful for particular scenarios only and tend that generally includes troubleshooting. One should be very careful while using them and be specific about the purpose for which they are being used. It is highly recommended that once testing of a SQL server trace flag is done, they should be disabled as soon as possible.