SQL Server 2005 Trace Flags FAQ

Trace Flags of SQL Server 2005 are a interesting topic. Even though Developer may not be using them much, Administrators and Performance tuning engineers regularly work them. Lets find out what are Trace Flags.

1. What are Trace Flags and why are they used?
2. What are the different types of Trace Flags?
3. What is the difference between Global Trace Flag and Session Trace Flag?
4. If a Session Trace Flag is enabled in Session A, will it affect Session B?
5. How to configure Trace Flags?
6. Is it possible to enable session Trace Flags using startup options?
7. How to enable the trace flag 3205 for a session?
8. How to enable the trace flag 3205 globally?
9. Where can I read more information on 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.

9. Where can I read more information on Trace Flags?

Read the MSDN article on Trace Flag