Home > Articles > Data > SQL Server

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020