Trace flags offer additional information about the SQL Server engine and enable or disable functionality in the SQL Server engine.
Some trace flags should only be set, if specific problems occur of if asked by SAP or Microsoft Support.
The following trace flags should not be set by default:
610, 611, 652, 661, 834, 1210, 1211, 1224, 3604
The following trace flags maybe set:
1204, 1222, 2549, 2562, 3226, 3605
Note: Not all of these trace flags are available on all SQL Server version, please check the documentation for details.
You can set traceflags permanently or temporarily.
To set the traceflags permanently, you have to set them as a startup parameter for SQL Server 2005 or higher:
a) Start the SQL Server Configuration Manager from the SQL Server Start menu.
b) On the left-hand panel, select “SQL Server Services” for your SQL Server.
c) On the right-hand panel, select the “SQL Server” service and in the menu choose ‘Action’ -> ‘Properties’.
d) On the ‘Advanced’ tab, choose the line ‘Startup Parameters’.
e) In the drop-down field, add the traceflag in the form -T<given traceflag> (e.g. ;-T3605).
Separate multiple entries with semicolon.
f) Apply the changes, and close the SQL Server Configuration Manager.
g) Restart the SQL Server.
On SQL Server systems that are running on a Windows cluster, the trace flags need only to be added to the active Node. The trace flags will be automatically replicated and added to the other nodes whenever SQL Server is started on those nodes.
To set the traceflag temporarily, use the command DBCC TRACEON:
use master dbcc traceon(<giventraceflags, separated by comma),-1> /* e.g.: dbcc traceon (3605,1204,-1) */ go
To check the traceflags, use the command DBCC TRACESTATUS:
use master dbcc tracestatus(-1) go
To disable a traceflag, use the command DBCC TRACEOFF:
use master dbcc traceoff(<traceflag to disable>,-1) /* e.g.: dbcc traceoff (1204,-1) */ go
