Traceflag activation / deactivation in SQL Server

What is a trace flag?

One option to modify MSSQL Server behavior is to use traceflags. You can compare a trace flag with the old DIP switches that you sometimes come across in equipment. At the moment there are already more than 100 traceflags and this number is only increasing.

There are traceflags that are described (“documented”) and thus supported by Microsoft, but there are also known “undocumented” traceflags. The use of undocumented traceflags carries a risk, as this can lead to a situation where support is no longer provided. Conversely, undocumented traceflags may only be activated on the advice of Microsoft. Always study the purpose and effects of a traceflag before trying it out in a test environment. DBA.nl can provide support with questions about the traceflags application.

How to activate?

As a hint in a query

With the T-SQL hint QUERYTRACEON a traceflag can only be activated for a specific query (in this example traceflag 4199):

SELECT … FROM … OPTION (QUERYTRACEON 4199)

Temporary – for a session

You can use a traceflag for a specific query by using the DBC TRACEON() command. An example to test traceflag 4199:

DBCC TRACEON(4199)
 SELECT …

When the session ends, the trace flag is turned off again. You can specify multiple trace flags, separated by commas.

Semi-permanent

The same command can be used to enable a traceflag globally. The flag then applies to all sessions. Then only -1 has to be added to the last trace number:

DBCC TRACEON(4199, -1)

This keeps the specified traceflag active until the instance is restarted. After the reboot, the traceflag is gone.

permanent

This means that the traceflag is added as a start parameter to the MSSQL service. The method differs per MSSQL version used, from MSSQL 2012 this has become a lot easier.

Changing Boot Parameters for MSSQL 2005 / 2008 (R2)

• Open SQL Server Configuration Manager
• Clock on “SQL Server Services” And right click “SQL Server (InstanceName)” to go to “properties”
• Go to the “Advanced” tab at properties
• Click on the value behind “Startup parameters” to get a dropdown menu. Add the trageflag with the -T (uppercase!) parameter. Put a semicolon in front of the traceflag. Replace xxxx with the desired traceflag number:

Changing boot parameters from MSSQL 2012

The procedure is broadly the same as previous versions, but a separate tab has been created for startup parameters. Add the parameter here, without a semicolon.

How do I know which traceflags are active?

With the command DBCC TRACESTATUS() you can request which traceflags are active and whether they are active for a session or globally. If you enter -1, you will see the global traceflags, otherwise you will only see your session traceflags. You can also provide a trace number to verify that it is active:

DBCC TRACESTATUS(-1)

Enable method traceflag

Because traceflags can be enabled globally, this is a handy method (in the example below we activate traceflag 4199)

• Activate the traceflag globally with the T-SQL command DBCC TRACEON(4199, -1)
• The trace flag is now active
• Also add the traceflag as startup parameter

How do I turn off a trace flag?

With the command DBCC TRACEOFF() a traceflag can be turned off for a session or globally. Add -1 to globally disable the traceflag:

DBCC TRACEOFF(4199, -1)

To remove a traceflag as a startup parameter, see the description how to add a traceflag. With MSSQL 2012 and higher you can go to the –T option and then click the button[remove] click. Be careful not to accidentally delete other startup parameters as well.