To demonstrate the third method of enabling trace flags, let's use one of the few documented trace flags, 1204, which is commonly used serverwide. Every time a deadlock occurs, this flag writes information to the SQL Server error log, including the processes, objects, and SQL queries involved in the deadlock. If you notice deadlocks occurring, enabling this flag serverwide is one of the first troubleshooting steps to take. For information about interpreting this trace flag's output, see the SQL Server Books Online (BOL) article "Troubleshooting Deadlocks."

To use the third method of enabling trace flags, you need to start SQL Server from a command prompt by invoking the SQL Server executable. This method is almost always used to enable the trace flag only for short-term testing. Here's an example of how to start a default SQL Server 2000 instance with the deadlock trace flag and trace flag 3605 enabled. Trace flag 3605 directs SQL Server to send the other trace flag's output to the error log (the output is usually written to the 'console' command prompt):

C:\Program Files\Microsoft SQL ServerMSSQL\binn\sqlservr   T1204  T3605

Note that you have to change the directory if your SQL Server isn't a default instance or wasn't installed in the default directory.

The fourth method is rarely used because it requires a lot more effort than the others and then isn't maintained after you stop and restart the SQL Server service. From the Services icon in Control Panel if you're using Windows NT 4.0, or from the Computer Management application in Win2K, you can find the entry for the SQL Server service. For SQL Server 7.0 and the default instance of SQL Server 2000, the service name is MSSQLSERVER. Double-clicking this service name brings up a dialog box that lets you supply a parameter, such as a trace flag, as long as the service is stopped. When you restart the service, the flag takes effect, but once you close the dialog box, the parameter value is lost.

Tracking Trace Flags
The last three methods of enabling trace flags require a parameter of -T to indicate that a trace flag is being set. SQL Server accepts a lowercase t (-t); however, -t sets other internal trace flags (besides the ones you intend to set) that SQL Server support engineers need for troubleshooting, so don't use it. The last three methods also apply the trace flags you specify serverwide.

The first method, DBCC TRACEON, can apply a trace flag serverwide if you add the parameter -1. But as I mentioned, it's difficult to have a single-session trace flag. SQL Server maintains a big bitmap to track whether each possible trace flag has been set, and for each trace flag, there's only one bit for the whole server. The bitmap also contains a bit for each connection, indicating whether any trace flags have been set for the connection. If one connection's bit is set, any flags that have been set for any other connection are available to the first connection. So you can't have just one trace flag enabled in one session and a different trace flag in another.

To see this behavior for yourself, you can use the DBCC command TRACESTATUS. With a parameter of -1, this command shows you any trace flags in effect for the connection you run it in:

DBCC TRACESTATUS(-1)

If you've enabled the deadlock trace flag 1204 serverwide, TRACESTATUS will show that 1204 is enabled for whatever connection you're using. Here's an example. Make sure that trace flag 1204 isn't enabled. Open Query Analyzer and execute DBCC TRACESTATUS(-1) to verify that no trace flags are enabled. You should see this message:

Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.

If you find that trace flag 1204 is on, you can disable it by using the command DBCC TRACEOFF(1204). Now enable 8602, then run TRACESTATUS again:

DBCC TRACEON(8602)
GO
DBCC TRACESTATUS(-1)

You should see this output:

TraceFlag		Status
-----		-----
8602		1

In a second window (a new connection), verify that no trace flags are enabled. Then, enable 8722 and run TRACESTATUS again:

DBCC TRACEON(8722)
GO
DBCC TRACESTATUS(-1)

This time, you should see that both 8722 and 8602 are enabled:

TraceFlag Status
-----	-----
8602	1
8722	1

If you return to the first connection and run DBCC TRACESTATUS(-1), you'll see that now that session has both flags enabled also. As soon as a connection has one flag enabled, all flags enabled for any other connections also apply. This behavior can be particularly overwhelming when you've globally enabled a flag such as 1204. In that case, every connection always has a flag enabled, so any additional trace flag that anybody turns on for any connection on the server will apply to all connections. I recommend that you always assume that any trace flag you enable might end up applying to every connection. If you don't want that behavior, don't use trace flags.

Trace flags can be useful for certain well-defined scenarios, mainly involving troubleshooting; they should never be something you play with just to see what they do. And always make sure you disable any trace flags when you're finished with your testing.

End of Article

Prev. page     1 [2]     next page -->



You must log on before posting a comment.

If you don't have a username & password, please register now.

 
 

ADS BY GOOGLE