Reporting Errors to Your Primary Support Provider

Troubleshooting SQL Server

Troubleshooting

Reporting Errors to Your Primary Support Provider

If you are unable to resolve a problem, contact your primary support provider for assistance. Anytime you receive a server internal error (for example, assertion or access violation (AV)), contact your primary support provider. If you experience an operating system or I/O error, it is most likely a hardware problem. Correct the hardware problem and restore your database. When reporting an error to your primary support provider, be sure to provide the Blackbox.trc and Sqldiag.txt files.

For more information about resolving a 9002 or 1105 space-related error, see Troubleshooting Recovery, Error 9002, and Error 1105.

Use sp_trace_create with the TRACE_PRODUCE_BLACKBOX option to define a trace that appends trace information to a blackbox.trc file in the \Data directory. Once the trace is started, trace information is recorded in the blackbox.trc file until the size of the file reaches 5 megabytes (MB). The trace then creates another trace file, blackbox_01.trc, and trace information is written to the new file. When the size of blackbox_01.trc reaches 5 MB, the trace reverts to blackbox.trc. Thus, up to 5 MB of trace information is always available.

Use the sqldiag utility to collect information about server version and configuration, .dll file version, error logs, extended stored procedures, operating system, computer version, configuration data, and additional data, all of which is put into Sqldiag.txt (by default located in \Mssql\Log). Also when sqldiag is executed, the two trace files blackbox.trc and blackbox_01.trc (if it exists) containing trace information including any server exceptions, are copied to the same output directory as sqldiag.txt (by default \mssql\log).

For example, the environment in which the error occurred includes this information:

  • Microsoft® SQL Server™ version number (as reported to the error log or returned by SELECT @@VERSION). The first message written to the error log provides the SQL Server version number.

  • DB-Library API version number (as reported by the SQL Server Client Network Utility), ODBC driver version (as reported by ODBC Driver Setup), or OLE DB provider information.

  • Application version number (for example, SQL Server Enterprise Manager or osql.exe).

  • Operating-system version number.

  • Hardware platform.

  • Production or development environment.

This additional information is also helpful in troubleshooting a problem:

  • The message number, message state, and complete error message text.

  • Any variables (numbers, database object types, or database object names) included in the error message.

  • The context in which the message was generated (what statement was running at the time).

  • The number of users who were logged in to SQL Server when the error occurred.

  • The frequency with which the error occurs.

  • If you are using SQL Query Analyzer and you do not see more than 255 characters in the results pane, click Tools, and then click Options. Click the Results tab, and then increase the value for Maximum Characters Per Column.

In addition, review the error logs and, if running Microsoft Windows® 2000 or Microsoft Windows NT® 4.0, review the Windows application log and the Sqlstp.log file, located in the \WINNT directory. You may also want to use SQL Profiler to monitor events.

To help resolve your problem quickly, you may be asked to send your Sqldiag.txt and error log in e-mail to your primary support provider.

When providing this information, provide the entire error log, including all messages displayed from startup to the very end of the log. Be sure to send all information beginning with the time of startup and ending with the error message in question.

Although most startup messages are identical each time you start SQL Server, additional messages sometimes appear during startup, which can provide clues for solving problems that occur during or after startup. If additional messages appear during startup, write down these error messages to assist your support provider in diagnosing and resolving the problem.

See Also

Monitoring with SQL Profiler