Enable \ Disable Trace flags in SQL Server

Trace flags are required for temporary purpose to analyze server level characteristics or behaviors during a course of action. It helps the DBA \ developers to troubleshoot the server. I’m not going deep into trace flag as this article I’m going to cover how to enable or disable trace flag for an instance of SQL Server. I’m getting this question from friends and colleagues frequently so drafting this article for you.

Applies to:

Applicable from SQL Server 2005 onwards

Enabling Trace Flags

There are three ways to enable trace flags in SQL Server

*) Add it in start-up parameter
*) Add it in registry (Not documented hence be cautious while going through this method, as usual don’t try this in production box)
*) Use DBCC Traceon

Add it in Start-up Parameter

This method will enable permanently, you need to remove it and restart to disable it.
*) Open SQL Server Configuration Manager
*) Click on “SQL Server Services” and on right hand pane go to properties of “SQL Server (InstanceName)
*) Go to “Advanced” tab of SQL Server properties window
*) Click on the drop down menu for “Startup parameters” and add the trace flag using -T parameter as shown below. Where xxxx is the trace number which you wanted to enable.

trace_flag_1

*) Now restart SQL Server service to enable the trace flag.

Add it in Registry

This is not recommended method as it’s not documented anywhere and don’t try to do it in production box. I’m just giving it for a reference so that you can try this if nothing works. This method will enable permanently, you need to remove it and restart to disable it.

*) Go to Run and type regedit.msc and press enter
*) Browse the registry path “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.XXXXX\MSSQLServer\Parameters
where XXXX is the instance name. For default instance it’s “MSSQLSERVER” for named instance its the name of your instance
*) Now to add the trace flag right click on the Parameters folder and click on New and then String Value
*) Provide the name as SQLArgN (where N is the incremental value from previous string value, in this case its 3) and then right click on the key value to modify the value.
*) Now provide the value as -TXXXX (where XXXX is trace number, in this case is 1204) as show below

trace_flag_2
*) Now restart SQL Server service to enable the trace flag

DBCC TraceON

Last two methods which we saw enables the trace flag permanently. Now the question is can I use trace flag for a session? Yes that’s possible we can make use of DBCC Traceon command. There two perspective you can enable it and they are below

Session Scope
You can use DBCC Traceon to enable trace flag for a single session so that it wont work in another session.

DBCC TRACEON(XXXX)

Global Scope
You can use DBCC Traceon to enable trace flag globally so that it will be effective in all sessions.

DBCC TRACEON(XXXX,-1)

where xxxx is your trace flag number

Disabling Trace flag

As we have 3 methods in enable we can use the same 3 methods for removal

Removing it from start-up parameter

*) Go to SQL Server configuration manager and go to SQL Server service property
*) Go to Advanced tab and click on drop down box of start-up parameter and remove the parameter -TXXXX
*) Restart SQL Server service

Removing it from Registry

*) Go to regedit.msc
*) Travel to the path “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.XXXXX\MSSQLServer\Parameters”
where XXXX is the instance name. For default instance it’s “MSSQLSERVER” for named instance its the name of your instance
*) Remove the string value which has -TXXXX as it’s value
*) Restart SQL Server Service

Removing it using DBCC Traceoff

Session Scope

Use the below command to turn off session scoped traces

DBCC TRACEOFF(XXXX)

Global Scope

Use the below command to turn off global scoped traces

DBCC TRACEOFF(XXXX,-1)

where xxxx is your trace flag number

How to check Trace flag Status

To know the status of any trace flag whether its turned on you can use the below command

To check individual trace flag status use the below command

DBCC TRACESTATUS(XXXX)

Where XXXX is trace flag number

trace_flag_3

To check what are all the trace flags are turned on use the command below. In the image below you can see that trace flag 1204 is enabled globally.

DBCC TRACESTATUS(-1)

trace_flag_4


Posted

in

by

Comments

4 responses to “Enable \ Disable Trace flags in SQL Server”

  1. Enable \ Disable Trace flags in SQL Server – SQL-Articles…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. balakiran avatar
    balakiran

    Thanks man, Very simple & easy to understand !!!!

  3. K.K.Khan avatar
    K.K.Khan

    Thanks man, Great help.

  4. Shamim Ahmed avatar
    Shamim Ahmed

    i couldn’t fine this anywhere else, thanks dude

Leave a Reply

Your email address will not be published. Required fields are marked *