Finding the active trace flags in Microsoft sql server

With help of below command we can find the active trace flags on Microsoft sql server instance.

DBCC TRACESTATUS

The output of the command is:

TraceFlag   Status Global Session

------------  -------- ----------  ----------

4199             1           1              0

4616             1              1              0

(2 row(s) affected )

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there are no active trace flags, we will get the output like:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Enable session based trace flags

To enable trace flags only in your session use the following two commands.

DBCC TRACEON (trace#)

DBCC TRACEOFF (trace#)

Enable the trace flags globally:

With help of below command we can enable the trace flags globally…

DBCC TRACEON (trace#, -1)

DBCC TRACEOFF (trace#, -1)

Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1205,2528)

To enable a trace flag to persist through a restart. Alter the “Startup Parameters” in the Advanced tab for the SQL Server service in SQL Server Configuration Manager.

For Example: Before Modification

-dc: \Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\ERRORLOG;

-lC:Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

After Modification:

-dc: \Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\ERRORLOG;

-lC:Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T4199

Available trace flages in sql server...