TRACE FLAGS

1. What are Trace Flags and why are they used?

Trace Flags are used to temporarily enable or disable specific behavior of SQL Server. For example, if the Trace Flag 7806 is set SQL Server 2005 allows Dedicated Administration Connections.

2. What are the different types of Trace Flags?

SQL Server 2005 supports Session level Trace Flags and Global level Trace Flags.

3. What is the difference between Global Trace Flag and Session Trace Flag?

Global trace flags are active at the server level and are visible to all the existing connections and new connection of the server.

Session level trace flags are active only to the current user session and they are visible to the current connection.

4. If a Session Trace Flag is enabled in Session A, will it affect Session B?

No. Session B will not be able to see any changes.

5. How to configure Trace Flags?

Trace flags are configured can be configured in two way. Using either DBCC TRACEON/TRACEOFF to configure session and global trace flags or -T startup option of SQL Server service to enable trace flags globally.

6. Is it possible to enable session Trace Flags using startup options?

No. It is not possible to enable session level trace flags using –T startup option. The startup option is used only to enable global trace flags.

7. Example: how to enable the trace flag 3205 for a session?

Execute the command DBCC TRACEON 3205

8. Example:how to enable the trace flag 3205 globally?

Execute the command DBCC TRACEON 3205, -1. Note the extra parameter -1 which instructs to apply the trace flag globally.