Customizing With Trace Flags

Trace flags are used to turn certain settings on or off in SQL Server. They can range from suppressing backup logs to affecting query plans. While some can be turned on from the beginning of a SQL Server install, you may be more likely to turn a trace flag on as a result of dealing with an issue, troubleshooting the issue, and then turning the trace flag back off.

Some trace flags are harmless but there are those that can cause more problems when used incorrectly. It’s important to note Microsoft’s statement that all documented trace flags are fully supported in a production environment “when used as directed.” A trace flag might only be helpful under very specific circumstances so make sure you read up on what you’re wanting to turn on before doing so.

Checking for What’s Enabled

What do you do if you want to see which trace flags are already enabled? If you want to check on a specific trace flag then you can run DBCC TRACESTATUS and specify the trace flag. Let’s say we want to check if trace flag 3226 is enabled:

DBCC TRACESTATUS (3226);
GO

Based on the 0’s, We can see that trace flag 3226 is not enabled globally or for our current session. To check all trace flags enabled on your SQL Server you can run the same as above except substituting a trace flag with -1;

DBCC TRACESTATUS(-1);
GO

Enabling Trace Flags in SSMS

Staying with trace flag 3226, if we’re wanting to turn this on for a short time, I would recommend running the following:

DBCC TRACEON (3226,-1);

We’ve specified the trace flag and included -1 to set it globally. If we run the above statement to turn on trace flag 3226, we can check the status and confirm that it is now enabled:

Enabling Trace Flags as Startup Parameter

If this is a trace flag that we want to stay enabled each time SQL Server restarts, we can include the trace flag as a startup parameter. We can go into SQL Server Configuration Manager, right click on our SQL Server, and select the Startup Parameters tab.

We’ll type “-T 3226” and click Add before clicking OK and closing out of SQL Server Configuration Manager.

Let’s confirm that we turned the trace flag off and then we’ll restart SQL Server to make sure it comes back on:

DBCC TRACEOFF (3226,-1);

DBCC TRACESTATUS (3226);
GO

If we restart SQL Server:

And connect again in SSMS, we can see that our trace flag has been enabled:

Trace Flags in Action

Coming up in the next few days, I’ll demonstrate the effects of trace flag 3226 and how it can save you some headaches when searching through SQL Server logs.

Thanks for reading!