How to enable and disable trace flags, or check for active trace flags in SQL Server

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Trace flags can be enabled on session level (active for a connection and are visible only to that connection) and on global level (set at the server level and are visible to every connection on the server).

Enabling and disabling trace flags on session level (you cannot enable a session-level trace flag by using a startup option):

DBCC TRACEON(trace_flag)
for example: DBCC TRACEON(3604)

DBCC TRACEOFF(trace_flag)
for example: DBCC TRACEOFF(3604)

Enabling trace flag on global level:

You can enable trace flag on global level with:

DBCC TRACEON(trace_flag,-1)
for example: DBCC TRACEON(1204,-1)

Or you can add the flag with –T switch to the startup parameters of the SQL Server Service (use an uppercase "T" to pass the trace flag number):

SQL Server 2012 and later versions:

right-click on the SQL Server service using Configuration Manager, select Properties and Startup Parameters:

startup parameters 1

Enter the startup parameter and click Add:

startup parameters 2

Startup parameter will be added to the list of parameters:

startup parameters 3

You will be required to restart your SQL Server service for this configuration change to take effect.

SQL Server 2008 R2 and older versions:

right-click on the SQL Server service using Configuration Manager, select Properties and the Advanced tab, and add for example:
;-T1204
to the Startup Parameters entry.

You will be required to restart your SQL Server service for this configuration change to take effect.

You can start the default instance of the SQL Server instance from the command prompt with trace flag 1204 enabled as follows:

NET START MSSQLSERVER /T1204

Disabling trace flag on global level:

DBCC TRACEOFF(trace_flag,-1)
for example: DBCC TRACEOFF(1204,-1)

Checking for active trace flags on the instance:

DBCC TRACESTATUS

If you get:

“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
then there are no enabled trace flags.