This article describes how to set the status of trace flags in SQL Server 2000 to on or off. It also describes what effects setting the status of a trace flag has (and what effects it does not have). Most of the information can be found in Books Online¹, though the information contained there is not complete and even confusing in some parts. In this article I have tried to present the complete information in a descriptive way.

About trace flags

Trace flags give us a way to configure some settings in SQL Server that make it behave differently when a specific flag is set. Some flags are used for enabling or disabling a feature, others give us a chance to look at what is happening ‘inside’ by outputting some information we can review. Books Online has this description for trace flags: “Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. […] Trace flags are often used to diagnose performance issues or to debug stored procedures or complex computer systems.”

In SQL Server 2000, trace flags are always set on or off globally for the entire server instance. Each session that connects to SQL Server is then either enabled or disabled from using trace flags. If a session is enabled it will be affected by all trace flags that have been activated. If it is disabled it cannot be affected by any trace flag.

Setting trace flags

Trace flags either have a status of on or off. There are two ways to set the status of a trace flag to on. One is by executing the command DBCC TRACEON, the other is to use a startup parameter for SQL Server. Regardless of how it is set on, a trace flag will stay on until it is manually switched off using DBCC TRACEOFF.

Using DBCC TRACEON to set a trace flag ON

DBCC TRACEON can be used to activate a trace flag. The full DBCC TRACEON syntax is:

DBCC TRACEON [ ( [ trace# [ ,...n ] ] [ , ] [ -1 ] ) ]

Note: This syntax differs slightly from the one in Books Online. Specifically, it includes the optional argument -1 and the fact that DBCC TRACEON can be executed with no parameters at all.

When executed with one or more trace flag numbers specified as arguments DBCC TRACEON will mark the status of those trace flags as on. The output from this command is simply a message that says “DBCC execution completed. If DBCC printed error messages, contact your system administrator.“. Note that there is no way to activate a flag only for the current session. Every other session that is enabled for using trace flags will immediately start being affected by the trace flags that where switched on.

The two other ways to use DBCC TRACEON (i.e. executing it with -1 or with no arguments at all) are described later when discussing how to enable sessions to use trace flags.

Using a startup parameter to set a trace flag ON

Trace flags can also be set ‘automatically’ by using the startup parameter -T#, where # symbolizes the number of the trace flag to set on. In general, this is used to set trace flags with effects that are not really specific to a user session (such as trace flag 1604, which makes SQL Server output information regarding memory allocation requests at SQL Server startup). Or it could be flags that the DBA always wants to have on. However, as seen below, these flags can be switched off just as easy as any other flag.

Using DBCC TRACEOFF to set a trace flag OFF

DBCC TRACEOFF is used to set the status of a trace flag to off. The syntax in Books Online is incomplete in the same way as for DBCC TRACEON. Here is the full syntax:

DBCC TRACEOFF [ ( [ trace# [ ,...n ] ] [ , ] [ -1 ] ) ]

Executing DBCC TRACEOFF with one or more trace flag numbers as arguments will deactivate the corresponding trace flags immediately, regardless of how they were activated. Again it is worth pointing out that the trace flags are always switched off ‘globally’. This means that not only the current session but also any other session will immediately stop being affected by the flags that where switched off.

Enabling and disabling sessions for using trace flags

A user session can either be enabled or disabled for using trace flags. If enabled it will be affected by every active trace flag (i.e. status ON), regardless of how they where set and by which user. If disabled they will not be affected by any trace flags. The default for new sessions is that they are disabled. So normally when a connection is made to SQL Server it will result in a session that is not affected by any trace flags. This default can be changed though, not by a configuration option but by certain actions. This section describes how to check if a session is enabled or disabled for trace flags, how to switch it and how new sessions are enabled or disabled by default.

Using DBCC TRACESTATUS to check the status of trace flags

The command DBCC TRACESTATUS is used to view the status of trace flags. Again slightly different from Books Online, the full syntax is:

DBCC TRACESTATUS ( trace# [ ,...n ] | -1 )

If executed in a session that is enabled for using trace flags it will return a resultset with two columns. The resultset contains one row for each specified trace flag. The first column shows the trace flag number and the second is a status value of 1 (on) or 0 (off). If DBCC TRACESTATUS is executed with -1 as argument the recordset will have one row for each trace flag that is active.

However, regardless of which arguments are used, if DBCC TRACESTATUS is executed in a session that is disabled from using trace flags it will not return any resultset. Instead it just prints the following message:

“Trace option(s) not enabled for this connection. Use ‘DBCC TRACEON()’.

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

Enabling the current session for using trace flags

As the message above says, we should use DBCC TRACEON to enable a session (connection) for using trace flags. Executing DBCC TRACEON with no parameters at all (and no parentheses, which just returns an error) enables the session it is executed in to start using trace flags. Note that this has no relation to whether or not there actually are any active trace flags. It only enables the session for using any existing and future active trace flags. The command returns no information (other than the standard DBCC message).

Executing DBCC TRACESTATUS(3604) (or similar) will now return a resultset with the status for the specified flags. That way we know that the session is now enabled for using trace flags. Note that executing DBCC TRACESTATUS with the -1 parameter will still not return a resultset (if no trace flags are set on). However since it will not print the message about the connection not being enabled for trace options either we still know that the session is enabled.

Also note that executing DBCC TRACEON with one or more trace flag numbers as arguments will also enable the current session to start using trace flags, apart from setting the status of the specified flags to on.

The following example shows this. Start Query Analyzer and execute the following:

-- Trace flag 3604 directs trace output to the client

DBCC TRACEON(3604)

-- View page information for a page

DBCC PAGE(master, 1, 1, 3)

Now open a second window and execute the following code, one line at a time:

-- No output shown, since this session is not affected by trace flags

DBCC PAGE(master, 1, 1, 3)

-- Enable current session for using trace flags

DBCC TRACEON

-- Now we get output from DBCC PAGE

DBCC PAGE(master, 1, 1, 3)

Disabling the current session from using trace flags

By executing DBCC TRACEOFF with no parameters at all the current session will be disabled from using trace flags. Other sessions will of course not be affected in any way since the status of any active trace flags is not changed. It is only the current session that will immediately stop being affected by trace flags. Also note that specifying a trace flag number as argument (like DBCC TRACEOFF(3604)) will not disable the session from using trace flags. It will only set the status of the specified trace flag off. This is true even if that trace flag was the last active trace flag.

Enabling all sessions for using trace flags

If -1 is specified as an argument for DBCC TRACEON then the current session and any other existing session will be enabled for using trace flags. This means that any active trace flags will immediately start affecting every session. Any future session will also be automatically enabled for using trace flags, so the default for new sessions has been changed.

The -1 argument can be specified together with trace flag numbers (like DBCC TRACEON(3604, -1)). In that case the trace flags will be activated and every session will start being affected by them. And if there already exists other active trace flags then the sessions will of course be affected by them as well.

Disabling all sessions from using trace flags

Just as specifying -1 as an argument for DBCC TRACEON enables all sessions to use trace flags, doing so for DBCC TRACEOFF disables all sessions from using trace flags. This includes the session where DBCC TRACEOFF(-1) is executed, any other existing sessions and all future sessions (by setting the default). Again, this does not set the status of any trace flag to off, so if a session later executes DBCC TRACEON to enable itself to use trace flags that session will immediately start being affected by any active trace flags.

A note about using the -T startup parameter

As said earlier, the startup parameter -T can be used to activate a trace flag automatically when SQL Server starts. It should be noted that this will also change the default setting for new sessions. Any session that connects to the server instance is therefore automatically enabled to use trace flags. Naturally this includes the trace flags that are activated at startup as well as any that are manually activated using DBCC TRACEON.

Summary

The following table describes the effects of different commands regarding trace flags:

Enabled indicates that the sessions are enabled for using trace flags. Disabled indicates that they are disabled from using trace flags. An empty cell indicates that the command does not affect the current setting for the sessions in that column, e.g. if they are already enabled they stay enabled and if they are disabled they stay disabled.

To sum up, trace flags in SQL Server 2000 are always set globally for the entire server. There is no way to set a flag for the current session only. A session that is connected to the server is either enabled or disabled for ’seeing’ (using) the trace flags that are active. If it is enabled it will automatically be affected by all active trace flags. New sessions that connect to the server are by default not enabled to use trace flags, but in some circumstances they will be.

Chris Hedgate (http://www.hedgate.net/blog/)

April 2005

References

1. Microsoft SQL Server 2000 Books Online (Updated - 2004)