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.
*) 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
*) 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
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)
Leave a Reply