SQL Trace Flag?
Trace flags are used to set temporary behavior or characteristics or to switch on or off particular behavior by DB engine. In particular when diagnosing the cause of error by some queries inside the DB engine.
Ways to do it?
*** user needs “sysadmin” fixed server role to enable or disable the traces.
There are 3 well know ways to enable “Trace”.
- Using DBCC TRACEON (XXX, XXXX, XXXX)
- Adding the trace flag at the startup Parameter
- Through Registry / setting the directory of SQL server from BINN folder.
To DISABLE trace DBCC TRACEOFF.
TO check the status of trace flags Globally DBCC TRACESTATUS.
Explaining each one below.
DBCC TRACEON (TRACE#1, TRACE#2, ……) [-1] [with NO_INFOMSGS];
Above syntax explains two ways of using the same DBCC to enable trace, locally only limited for current session once closed the session trace is not enabled any more, and by using -1 with the DBCC command it will enable through globally (server level and are visible to every connection on the server).
As a STARTUP PARAMETER
To do this we need to stop and start the ending using configuration manager. And *this needs to separated by “;-TXXXX “ semi-colon used to separate the trace flag from before parameters used to start up the engine this is mandatory.
Properties àAdvancedàStart Parametersà -d— followed by the binn path where you have placed the master mdf, Errorlog and master log file locations followed by terminated by “;” and then trace flag.
After setting this need to restart the service and then the trace will take effect globally.
Enable Trace at the registry (undocumented)
This is will enable trace permanently equal effect as the startup parameter.
Need to restart after edited the registry parameter.
Using “regedit” at run open up the registry tree , and dig down to “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.XXXX\MSSQLServer\Parameters” where XXXX is the instance name , for default it is MSSSQLSERVER , 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).
Tracking the trace flags use
***NOTE never use –t how ever this is accepted by sql server engine it will also enables few of the internal trace flags which might collapse if not handled properly, primarily used by engineers to diagnose the engine issues.
Links useful for supporting and reading,