The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.
Trace flags are valuable DBA tools that allow you to enable or disable a database function temporarily. Once you turn on a trace flag, it remains on until you either manually turn it off or restart SQL Server. Some trace flags, like deadlock detection, should begin on startup.
Most trace flags are undocumented in SQL Server 2000, because they can slow down your system or allow you to make foolish decisions. In this section, I'll cover flags I've found to be useful, some of which are undocumented.
Setting Trace Flags
Turning on trace flags and setting them can be a little tricky, especially at a user level. You can turn on trace flags any one of four ways:
Only users in the sysadmin fixed server role can turn on trace flags.
I prefer to generally stick with the first two options of setting trace flags. It's especially easy to turn on a trace flag using the DBCC TRACEON command. For example, if you want to enable trace flag 1807, use the following syntax:
Use the DBCC TRACEOFF command to disable traces.
For either command, target multiple traces by separating each trace with a comma:
DBCC TRACEOFF(1807, 3604)
In the previous examples, this will only set the trace for the current connection (there are exceptions to this rule that I'll discuss in a moment). If you'd like to set the trace using the DBCC TRACEON command at a server level, you can use the -1 switch as shown here:
DBCC TRACEON (8602, -1)
Checking for Running Traces
When you enable traces, it's a good idea to see if other traces are currently running on your system. If you're looking for a specific trace, use the DBCC TRACESTATUS command followed by the list of trace flags you want to check on. For example, the following syntax checks for trace flags 3604 and 1807:
DBCC TRACESTATUS(3604, 1807)
The resulting output displays in two columns: the trace flag is in one column, and the status (0 for off and 1 for on) is in the other column. It's not uncommon to be totally unaware of which traces are active. To enumerate a complete list of traces that are on, use the DBCC TRACESTATUS command followed by the (-1) parameter:
Starting Traces Automatically
You can start traces automatically when starting SQL Server by using the -T switch. Set the trace flags you want to execute at startup in the Startup Parameters dialog box (see Figure 5-9), which you reach by selecting Startup Parameters in the General tab of the Server Properties dialog box.
Figure 5-9Adding trace flags to execute at startup
In the Trenches
I alluded to a few moments ago an interesting quirk with SQL Server. The quirk appears when you want to set a trace flag at an individual connection level. When this is set, SQL Server will apply the trace flag against anyone who has turned on an individual connection trace flag. For example, let's say you turned on trace flag 8602 for an individual connection:
DBCC TRACEON (8602)
Another user then turns on trace flag 8755 for his individual user connection: DBCC TRACEON (8755)
You can then see the status of the trace flags and see that both are indeed turned on for each of the users. DBCC TRACESTATUS (-1) will result in: TraceFlag Status
Apply Traces Among All Connections (-1)
Normally, a trace that is set in Query Analyzer only applies at the client level. If you use the undocumented trace flag of -1, the system applies any traces you've set across all active and new connections. This trace is not reported when you use the DBCC TRACESTATUS command.
Deadlock Information (1204)
This commonly used trace flag detects deadlocks and outputs the deadlock information. I'll cover much more detail about deadlocks and this trace flag in the next chapter.
Detailed Deadlock Information (1205) This trace flag sends detailed information about the deadlock to the error log.
Disable Parallel Checking (2528)
You can use this trace flag to disable SQL Server from using any processor other than the primary processor when performing consistency checks (DBCCs). If you have a multiprocessor machine running a DBCC command, enabling this flag worsens performance considerably. (SQL Server uses multiple processors for running a DBCC command starting with SQL Server 2000.)
Network Database Files (1807)
SQL Server will not allow you to create a database or log file on a networked drive by default. If you attempt to do this, you receive error 5105, which states "Device Activation Error." The 1807 trace flag provides a workaround for this restriction, and you can create database files on a mapped drive or UNC path. However, just because you can do something doesn't mean you should. This trace is undocumented for a good reason. It is an incredibly bad idea to place a database file on a network drive. The support for this feature was added to help vendors like Network Appliance (http://www.netapp.com/). Storing data on a network drive opens another potential can of worms, because you must monitor and provide redundancy for that drive. If network connectivity is interrupted, your database goes down.
Send Trace Output to Client (3604)
Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag.
Send Trace Output to Error Log (3605)
This trace is similar to trace flag 3604, but this flag sends the results to the error log.
Skip Automatic Recovery (3607)
Trace flag 3607 skips the recovery of databases on the startup of SQL Server and clears the TempDB. Setting this flag lets you get past certain crashes, but there is a chance that some data will be lost.
Skip Automatic Recovery Except Master (3608)
This trace is similar to 3607, but the TempDB in this case is not cleared and only the master database is recovered.
Log Record for Connections (4013)
This trace flag writes an entry to the SQL Server error log when a new connection is established. If you set this option, your error log can fill up quickly. For each connection that occurs, the trace flag writes two entries that look like this:
Login: sa saSQL Query Analyzer(local)ODBCmaster, server
process ID (SPID): 57, kernel process ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster,
server process ID (SPID): 57, kernel process ID (KPID): 57.
Skip Startup Stored Procedures (4022)
This is a handy trace flag for troubleshooting. It forces SQL Server to skip startup stored procedures. This is especially useful if a stored procedure has been altered and causes harm to your system. After you set this trace flag, you can then debug the stored procedure and set it back to its original state.
Ignore All Index Hints (8602)
Trace flag 8602 is a commonly used trace flag to ignore index hints that are specified in a query or stored procedure. This is a fantastic option when you're trying to determine if an index hint is hurting more than helping. Rather than rewriting the query, you can disable the hint using this trace flag and rerun the query to determine if SQL Server is handling the index selection better than the index hint.
Disable Locking Hints (8755)
Trace flag 8755 will disable any locking hints like READONLY. By setting this, you allow SQL Server to dynamically select the best locking hint for the query. If you feel the query's locking hint may be hurting performance, you can disable it and rerun the query.
Disable All Other Hints (8722)
Lastly, the 8722 trace flag will disable all other types of hints. This includes the OPTION clause.
By running all three 8602, 8755, and 8722 trace flags, you can disable all hints in a query. If you feel your performance is being negatively affected by a hint, you can set these rather than rewrite all the queries while you test. Generally speaking, there's no reason to place hints on queries in SQL Server 7.0 or 2000.
Click for the next excerpt in this series: Alerts
Click for the complete book excerpt series.