Trace flags can be used for many things. They can be used to capture information about what is happening. For example trace flags 1204 and 1222 can be used to capture information about deadlocks. Trace flags can be used to change the behavior of SQL Server for example, trace flag 3226 can be used to suppress successful backup messages in the error log. You can even use trace flags to change the behavior of the TempDB, trace flags 1117 and 1118 can help keep the TempDB files close to the same size by changing how the data files grow.
There are many more trace flags that can be used. Here is a link to a Microsoft document that describes each of the trace flags, click here.
In order to use trace flags they must be enabled first.
The first step would be to determine which flags are currently enabled. This can be done with the DBCC TRACESTATUS statement.
If you run this statement to see what trace flags are enabled.
Notice the -1 in the parameter, the value tells SQL Server to return information on all enabled trace flags. You will get one of two results. You will either get the list of trace flags that are enabled or you will get this statement.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If you receive the above statement, it means that there are not any trace flags enabled at the moment. However, let’s say I have a number of trace flags enabled and I just want to check the status of one or more specific trace flags, you can use the code below.
DBCC TRACESTATUS (2528, 3205, 1204);
This statement accepts the trace flags you are looking for in a comma separated list. In in the above statement you will have a set of three rows returned. The results are below. Notice there are 4 columns, the TraceFlag number, status, global and session. The trace flag number is obvious as is the status. In the status column it will either be a 1 or 0, enabled or disabled.
This brings us to the point of this post. The third and forth column of the result set, Global and Session. These columns identify if the trace flag is turned on globally or just for the current session.
If you run this code the trace flag will be turned on for the session.
This can be confirmed by running this.
DBCC TRACESTATUS (2528, 3205, 1204);
Notice that the value for the 2528 trace flag for Session is set to TRUE.
So what does it mean to when a trace flag is turned on just for a session? It means that the trace flag is only active for the current session and is not visible from other sessions. The image below show that 2528 is not active. The trace flag was enable for SPID 62 and can not be seen from session 69.
In order to enable the trace flag globally, you will need to make one simple change to the DBCC statement.
DBCC TRACEON(2528, -1)
You simply have to add the -1 to the TRACEON statement as in the above statement. When you run TRACESTATUS again, you will see that the flag under Global has changed to true. This trace flag was enabled for SPID 62.
If I run TraceStatus in SPID 69, I see that the session is indeed enabled globally. Notice that trace flag 2528 is not enabled for SPID 69, remember we enabled that only for the session, in this case SPID 62.
You may have the question of, what happens when I restart the SQL Server service. The answer is simple, the trace flags will no longer be enabled. If you would like to have the trace flags enabled upon start up, you can add the -T command line startup option for SQLServr.exe. One thing that is important here is for you to assure that the trace flags you would like to have enabled to be enabled again when the service is restarted.
Per Microsoft, this is a best practice, link to MS. In this same document, Microsoft recommends that you not run TRACEON while users are connected to the system, otherwise you could see unpredictable behavior.
Now let’s say you want to disable the trace flags. To do this you will need to use the TRACEOFF statement, like the one below. Notice the -1 parameter is included when you want to turn the trace off globally.
DBCC TRACEOFF(2528, -1)
To turn off a trace flag for a session use this
You can also enable and disable multiple trace flags with the same statement. The desired trace flags will need to be listed and separated by a comma.
Here is an example of how to enable multiple trace flags at the same and as well as check the status.
DBCC TRACEON(2528, 3205, 1204, -1)
DBCC TRACESTATUS (2528, 3205, 1204 );
Now comes the challenge, what trace flags should I enable. There are many posts on different blogs that provide many great recommendations. In my opinion, before enabling a trace flag you should thoroughly understand what it does and if there is a potential for a negative impact on performance.
Thank you for stopping by my blog and hopefully you learned something.