Trace flag is used to set up some specific server feature or behavior. For example the 3605 would output the error information to the error log.
To check the trace flag status:
-- Show trace flag status in the current session
-- Show trace flag status for the global
-- Show whether trace flag 3205 is enable at current session
DBCC TRACESTATUS( 3205);
We can enable the trace flag via 2 ways
1. Add the trace flag in the start up parameter list
Below example enable the trace flag 1204 for dead lock information (1204) and output to the error log (3605).
The trick for this is that “T” must be capital. Even MSFT support use –t, it can be only used if MSFT support advise to do so.
When enable it through the start up parameter, it is affect at global level and it would require the bounce of SQL Server to make it effective.
-T 1204;-T 3605
2. Use DBCC TRACEON
-- Enable 1222 and 3605 at current session
DBCC TRACEON (1222,3605);
-- Enable 1222 and 3605 at global level
DBCC TRACEON (1222,3605,-1)
When use DBCC TRACEON on the global level it would not affect any existing session. Therefore MSFT suggest only use this to while application is not currently running on the server in case the the inconsistence for the application behavior.