You can use the DBCC TRACEON command to turn on the specified trace flag. This is the syntax:
DBCC TRACEON (trace# [,…n][, -1]) [WITH NO_INFOMSGS]
If you want to turn off the specified trace flag(s), you can use the DBCC TRACEOFF command.
This is the syntax:
DBCC TRACEOFF (trace# [,…n] [,-1]) [WITH NO_INFOMSGS]
You can use the DBCC TRACESTATUS command to get the status information for the particular trace
flag(s) currently turned on. This is the syntax:
DBCC TRACESTATUS ([ [trace# [,…n] ] [,] [-1] ]) [WITH NO_INFOMSGS]
To get the status information for all trace flags currently turned on, you can use -1 for trace#.
This is the example:
DBCC TRACESTATUS (-1)
Useful trace flags
1. Trace flag 1204.
This trace flag returns the type of locks participating in the deadlock and the current command
affected. This trace flag is documented in SQL Server 2012 Books Online. This is global trace flag.
2. Trace flag 1205 (undocumented).
This trace flag returns more detailed information about the command being executed at the time
of a deadlock. This trace flag was documented in SQL Server 7.0 Books Online, but is not
documented in SQL Server 2012.
3. Trace flag 1211.
This trace flag disables lock escalation based on memory pressure, or based on number of locks.
If turns on, then SQL Server 2012 will not escalate row or page locks to table locks.
Note. When the trace flag 1211 is turned on then excessive numbers of locks can be generated.
4. Trace flag 1224.
This trace flag disables lock escalation based on the number of locks. If both trace flag 1211
and 1224 are set, 1211 takes precedence over 1224.
5. Trace flag 1807 (undocumented).
You cannot create a database file on a mapped or UNC network location. This opportunity is
generally unsupported under SQL Server 2012. However, You can bypass this by turn on trace
6. Trace flag 2508 (undocumented).
This trace flag disables parallel non-clustered index checking for DBCC CHECKTABLE.
7. Trace flag 2528.
This trace flag disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP,
and DBCC CHECKTABLE. Usually trace flags 2508 and 2528 should not be used because checking in
parallel provides better performance, but sometimes when another process requires CPU resources
during checking, you can disable parallel checking to free some CPU resources for another process.
8. Trace flag 3205.
This trace flag disables hardware compression for tape drivers. You can use this trace flag
if your tape drives do not support compression.
9. Trace flag 3608.
This trace flag skips automatic recovery (at startup) for all databases except the master
database. To turn on this trace flag, you must use -T startup option. For example, you should
start SQL Server 2012 with the following parameter: -T 3608
To make it, run Services from the Control Panel, double click on MS SQL Server service, click
Stop button, then specify parameter value (-T 3608) and click the Start button.
10. Trace flag 3609 (undocumented).
This trace flag skips the creation of the tempdb database at startup. To turn on this trace
flag, you must use -T startup option.
11. Trace flag 4616.
This trace flag makes server-level metadata visible to application roles. By default, an
application role cannot access metadata outside its own database. This is global only trace flag.