Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Troubleshooting Server Problems
When SQL Server fails, it's often difficult to know where to start in terms of fixing it. This section examines how you can interpret SQL Server's Error log. Then, this section discusses something called trace flags, which you may be able to use to get information about what's happening. This section also shows you how to get the diagnostics you need when you call Microsoft Technical Support and how you can turn on SQL Server's "flight recorder." Most server troubleshooting is done "by guess and by golly," and there aren't many tools to help you. In the main, Enterprise Manager is useless; in fact, because it consumes so many resources, it may not even start when your server is having problems. You need to be comfortable with Query Analyzer, as well as operating system tools, such as the NT Event Viewer, and the always, reliable Notepad as well as the DOS prompt!
Error Log
SQL Server maintains a log of error and information messages. By default, this log is kept in the \MSSQL7\LOG folder, although you can change this by changing the location on the SQL Server startup (see the "Startup Switches" section earlier in this chapter). When you start SQL Server, SQL Server creates a new log and archives the old one. When you look in the folder where the Error logs are kept, you see the following files (assuming, for this example, that you have started SQL Server at least seven times):
|
File |
Description |
|
ERRORLOG |
Current error log |
|
ERRORLOG.1 |
Most recent error log |
|
ERRORLOG.2 |
Second most recent log |
|
ERRORLOG.3 |
Third most recent log |
|
ERRORLOG.4 |
Fourth most recent log |
|
ERRORLOG.5 |
Fifth most recent log |
|
ERRORLOG.6 |
Oldest log |
If your server crashed, and you have restarted it, you may find the reason for the crash in ERRORLOG.1; if you haven't been able to restart it, look at ERRORLOG. You can view Error logs in Notepad. (It's also possible to view Error logs in Enterprise Manager; the logs are visible in the MANAGEMENT folder.) Many of the messages in the Error log are also visible in NT's Application Event log.
When you start SQL Server, it records the success and/or failure of its attempts to recover databases in the Error log. Normally, you see a series of messages and discover that all databases have been successfully recovered. Occasionally, you see a message that a database has been marked suspect. This usually means that one of the files that make up the database has been damaged or removed, and you will need to correct that problem. If you see errors that are Exception Access Violations, there is probably a bug in SQL Server and you will need to contact Microsoft Support. Other messages appear from time to time; for example, anytime that someone performs a non-logged operation, a message to that effect appears in the Error log. Following is one message that users often find troubling, but you shouldn't worry about it:
Failed to obtain TransactionDispenserInterface: XACT_E_TMNOT AVAILABLE.
This message means that the MSDTC service isn't running. If you're not performing distributed transactions, you don't need MSDTC.
If your server crashes, you should start it in single-user mode and check the Error log to make sure that all the databases were successfully recovered and that there are no problems before you make the server available for general use.
Errors issued by applications in your organization may appear in the Event log as well. (Chapter 8 discusses this in more detail.)
Trace Flags
Trace flags are used to temporarily change the characteristics of SQL Server, or to turn off some particular behavior. Some trace flags are available for backward compatibility with previous versions of SQL Server. They can also provide additional information about locks, deadlocks, and query plans. Microsoft sometimes provides trace flags as a way of working around a bug. Trace flags normally write information to the Error log; there's a trace flag that you can use to override this and send the information back to a client. A few of the more useful trace flags are discussed here; you can get details about others from Books OnLine.
Trace Flags That Provide Detailed Information About Locks and Query Plans
Chapter 10, "Performance Tuning," discusses query plans in more detail, but the trace flags listed here can give you additional information when you are studying performance or contention problems:
|
Flag |
Meaning |
|
325 |
Prints information about the cost of using a non-clustered index or a sort to process an ORDER BY clause |
|
326 |
Prints information about the estimated and actual cost of sorts |
|
330 |
Enables full output when using the SET SHOWPLAN option, which gives detailed information about joins |
|
1204 |
Returns the type of locks participating in the deadlock and the current command affected |
|
1205 |
Returns more detailed information about the command being executed at the time of a deadlock |
Managing Tape Compression
It's often desirable to compress data that is written to tape so that it takes up less space. By default, if a tape drive supports hardware compression, backups will be written in a compressed format. You can use the following trace flag to disable this compression if you want to exchange tapes with other sites or use other drives that don't support compression.
|
Flag |
Meaning |
|
3205 |
Disable hardware compression |
Managing Trace Flags
As mentioned earlier, many trace flags send output to the Error log. You may want the output to come directly to you. Two trace flags control where the output is sent.
|
Flag |
Meaning |
|
3604 |
Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF. |
|
3605 |
Sends trace output to the Error log. (If you start SQL Server from the command prompt, the output also appears onscreen.) |
Suppressing Unneeded Messages
The following trace flag that suppresses unneeded messages is an important flag, and you may want to include it on the SQL Server startup. Any time a statement that does not return results is executed in a stored procedure, SQL Server sends a Done in Proc message back to the client. For example, the following statement sends such a message:
IF @@error <> 0
|
Flag |
Meaning |
|
3640 |
Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT; but when set as a trace flag, every client session is handled this way. |
The client has absolutely no interest in the Done in Proc message. It is a short message and can create bottlenecks across WANs. It also impairs the performance of stored procedures when they are run through the Job Scheduler.
Using Trace Flags
You can use trace flags in two ways:
As a SQL Server startup parameter
With DBCC TRACEON/OFF
To specify a trace flag as a SQL Server startup parameter, use –T or /T followed by the trace flag, as follows:
/T3640
Be sure to use a capital T; lowercase is for Microsoft internal trace flags.
To set a trace flag with DBCC, use the following command:
DBCC TRACEON (trace# [,...n])
If you are studying a problem and want the output to come back to the client (Query Analyzer, for example) instead of being written to the Error log, you should issue the following commands:
DBCC TRACEON (3604) DBCC TRACEON (1205)
To turn off a trace flag, use the following command:
DBCC TRACEOFF (trace# [,...n)
To determine whether a trace flag is on or off, use the following command:
DBCC TRACESTATUS (trace# [,...n)
The resulting display shows the trace flag and a 0 if it is off, or a 1 if it is on.
Diagnostics
When you encounter problems with SQL Server, you need information that will help you solve the problem. If you have to contact Microsoft, they will ask you about your server configuration and want details as well. You have two ways of getting this information:
The sqldiag utility
SQL Server's "flight recorder"
You can also use these tools to collect information whether or not you are having problems. The flight recorder can help you isolate problem queries, and the output from sqldiag can provide a record of configuration settings, and other items as detailed in the following sections.
sqldiag
The sqldiag utility comes with SQL Server and gives you information whether or not SQL Server is running. When SQL Server is running, the report includes the following:
Text of all Error logs
Registry information
DLL version information
Output from the following:
sp_configure
sp_who
sp_lock
sp_helpdb
xp_msver
sp_helpextendedproc
sysprocesses
Input buffer SPIDs/deadlock information
Microsoft Diagnostics Report for the server, including the following:
Contents of <SERVERNAME>.TXT file
Operating system version report
System report
Processor list
Video display report
Hard drive report
Memory report
Services report
rivers report
IRQ and port report
MA and memory report
nvironment report
Network report
The last 100 queries and exceptions (if you have turned on the recording of these)
If SQL Server is not running, information about SPIDs, configuration information, and the output of the various stored procedures is not included. To run the diagnostics program, issue the following command at a DOS prompt:
sqldiag [{-U login_ID] [-P password] | [-E]}] [-O output_file]
You must run sqldiag on the server; you cannot run it from a client workstation. You can specify a login ID and password, or use the –E switch to specify a trusted connection. By default, the output files are named SQLDIAG.TXT and SQLDIAG.TRC (if the query recorder is running) and are placed in the MSSQL7\LOG folder. You may specify a different name and location.
The Flight Recorder
It's possible to have SQL Server save the last 100 queries. If you do this, they are included in the output from sqldiag. The sqldiag utility produces a file that can be read by SQL Profiler. You can also have this file produced at any time (without running sqldiag) after the flight recorder has been enabled. The queries are stored in a wraparound list; when query 101 comes along, it overwrites query 1 in the list. To turn on the flight recorder, issue the following command:
xp_trace_setqueryhistory
After you have issued the command, xp_trace_setqueryhistory stored procedure is automatically executed each time SQL Server starts. To turn off the recorder, issue the following command:
xp_trace_setqueryhistory
You do not have to run sqldiag to find the most recent queries. Instead, you can issue the following command:
xp_trace_flushqueryhistory 'filename'
With this command, you can specify a full path as part of the filename. After the file has been created, you can view it using SQL Profiler. (See Chapter 10 for more information on SQL Profiler.)
General Tip
I could not get the flight recorder to work until I stopped the server and restarted it. The commands appeared to work, but no query history file was created. The documentation did not mention the need to stop the server and restart it after issuing the xp_trace_setqueryhistory command.
After you have enabled the query history, SQL Server saves a trace automatically whenever there is a SQL Server–issued error with severity level 17 or higher. Note that if an application raises an error of level 17 or higher, the trace is not written. The trace is written to a file named BLACKBOX.TRC and saved to the \MSSQL7\LOG folder.