SQL Server alerts you to possible problems with databases, objects, or the server, but how do you in determine and fix the problem. The first step to troubleshooting problems is to understand how to read and where to find SQL Server error messages.
If you run a query and accidentally make a mistake by entering a table that does not exist in the database, what happens? SQL Server returns an error message. Actually, SQL Server reacts to all errors in the same manner, whether those errors are generated by users, databases, objects, or the system. SQL Server returns a formatted error message and/or writes the error message to the error log and/or event log. Here is a quick example that executes a SQL statement to update a nonexistence table in the pubs database. The SQL statement for the example is as follows:
Set author1 = "Spenik",
author2 = "Sledge",
title="Microsoft SQL Server DBA Survival Guide"
When the statement is executed, the following error message is returned:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'new_authors'.
The preceding error message demonstrates the standard message format for error messages returned by SQL Server.
The first thing presented in the error message is the message number, severity level, state, and line number. To most users, these numbers are just garbage to be ignored, so they skip down to the message and try to resolve the problem. In reality, the error message number is very useful for obtaining more error information. You can use the severity levels to help find errors that need to be handled. When tracking a problem, always write down all the error information, including the message number, severity level, and state. In many cases, these will be of more assistance than the actual message.
Examine the format of a standard SQL Server error message.
Error Message Number
Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error.
You can define your own error messages. User-defined error message numbers must be greater than 50,000 and less than 2,147,483,647
The error severity levels provide a quick reference for you about the nature of the error. The severity levels range from 0 to 25.
|0 to 10
||Messages with a severity level of 0 to 10 are informational messages and not actual errors.
|11 to 16
||Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.
||Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.
||Severity level 18 messages indicate nonfatal internal software problems.
||Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.
Severity errors 19 through 25 are fatal errors and can only be used via RAISERROR by members of the fixed database role sysadmin with the with log option required. Severity 0 - 18 can be used by all users. When a fatal error occurs (20 - 25), the running process that generated the error is terminated (nonfatal errors continue processing). For error severity levels 20 and greater, the client connection to SQL Server is terminated.
||Severity level 20 indicates a problem with a statement issued by the current process.
||Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.
||Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.
||Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.
||Severity level 24 indicates a hardware problem.
||Severity level 25 indicates some type of system error.
The error state number is an integer value between 1 and 127; it represents information about the source that issued the error (such as the error can be called from more then one place).
The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table. Figure 1 shows a query result of the sysmessages table.
Figure 1 - Query results of sysmessage using the Query Analyzer.
To use the SQL Enterprise manager to view error messages or search for error messages, select a server and right-click. Select All Tasks and Manage SQL Server Messages, and the SQL Server Message dialog box appears. Using the dialog box, you can search for error messages by error number or key words.
Using the Error Message Number to Resolve the Error
Earlier in this article, you learned that, by using the error message number, you could quickly retrieve detailed information about the error and possible ways to resolve the error. How, you might ask? Books Online!
When you installed SQL Server, you should have included the Books Online utility shown in Figure 2.
Figure 2 - The SQL Server Books Online dialog box.
To see how to use Books Online to find more information on the error message number displayed during the invalid query example (error message number 208), follow these steps:
- From the Windows NT or Windows 9x start menu, select the SQL Server 2000 program group and select Books Online. The SQL Server Books Online dialog box, shown in Figure 15.2, appears. From the SQL Server Books Online dialog box, click the Search Tab, shown in Figure 3.
Figure 3 - The Search tab.
- The Search tab enables you to quickly search Books Online for specific information. In the Query combo box, type the error message number: 208. In the Topic Area To Search frame, select the Title Only check box.
- To run the search, click the List Topics button. The query runs, searching for 208 in the title of any of the book topics. If one or more items are found, they are displayed in a Query Results frame in the SQL Server Books Online dialog box (see Figure 4).
Figure 4 - Query results frame in the SQL Server Books Online dialog box.
- To view the document(s) found in the search, double-click the item or select the item and click the Display button. The detailed information for the error message number, including a detailed explanation and the action to take, is displayed in the document. You can even print the document! Just think, no more trying to locate a troubleshooting or error message book! No more flipping through pages searching for error messages; if the error number is not in the book, you know immediately! When getting multiple documents back for an error message query, select the document title Error Error_Number. For example, the document title Error 208 displays detailed information on error 208. The detailed information found for error number 208 is displayed in Figure 5. After you have displayed the error number document, read through the document for an explanation of the error and then follow the directions in the Action section of the document to correct the error.
Figure 5 - The Books Online description of error message 208.
Deciphering the Error Log
The error log is a standard text file that holds SQL Server information and error messages. The error log is used both in Windows NT systems as well as Windows 9x systems. The error log can provide meaningful information to help you track down problems or to alert you to potential or existing problems. SQL Server maintains the current error log and a default value of six previous error log files. SQL Server 2000 allows you to configure the number of previous log files to maintain before reusing them. The current error log filename is ERRORLOG; the previous error log files, referred to as archived error logs, are named ERRORLOG.1 (most recent) to ERRORLOG.6 (the oldest). The default location of the error log file is in the \LOG directory off the SQL Server home directory, which is under Program Files\Microsoft SQL Server\Instance Name. The following is an example of a SQL Server error log:
2000-09-01 21:49:31.08 server Microsoft SQL Server 2000 - 8.00.100 (Intel X86)
Apr 18 2000 01:19:00
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows 4.10 (Build 1998: )
2000-09-01 21:49:31.10 server Copyright (C) 1988-2000 Microsoft Corporation.
2000-09-01 21:49:31.10 server All rights reserved.
2000-09-01 21:49:31.10 server Server Process ID is -676985.
2000-09-01 21:49:31.10 server Logging SQL Server messages in file
2000-09-01 21:49:31.33 server SQL Server is starting at priority class
[ic:ccc]'normal'(1 CPU detected).
2000-09-01 21:49:32.01 server User Mode Scheduler configured for thread
2000-09-01 21:49:32.12 server Using dynamic lock allocation.
[ic:ccc]  Lock Blocks,  Lock Owner Blocks
2000-09-01 21:49:32.53 spid3 Starting up database 'master'.
2000-09-01 21:49:34.15 spid3 0 transactions rolled back in
[ic:ccc] database 'master' (1).
2000-09-01 21:49:34.22 spid3 Recovery is checkpointing database 'master' (1)
2000-09-01 21:49:34.70 server Using 'SSNETLIB.DLL' version '8.0.100'.
2000-09-01 21:49:34.95 spid3 Server name is 'MSPENIK'.
2000-09-01 21:49:35.14 spid7 Starting up database 'msdb'.
2000-09-01 21:49:35.33 spid8 Starting up database 'pubs'.
2000-09-01 21:49:36.00 server SQL server listening on TCP port 1433,
[ic:ccc] Shared Memory.
2000-09-01 21:49:37.02 spid5 Clearing tempdb database.
2000-09-01 21:49:40.11 spid5 Starting up database 'tempdb'.
2000-09-01 21:49:40.61 spid3 Recovery complete.
2000-09-01 21:50:53.58 spid51 Using 'xpstar.dll' version '2000.80.100'
[ic:ccc] to execute extended stored procedure 'sp_MSgetversion'.
The error log output includes the time and date the message was logged, the source of the message, and the description of the error message. If an error occurs, the log contains the error message number and description.
You can view the error log using the SQL Server Enterprise Manager. To use the Enterprise Manager, simply expand the error log tree located in the management folder on the server you want to view, expand the SQL Server Logs icon and then select the error log you want to see. The error log is loaded into the right hand frame of the SQL Server Enterprise manager as shown in Figure 6.
Figure 6 - The Server Error Log displayed in the SQL Server Enterprise Manager.
The error log can be a useful source of information in certain problems. For example, if SQL Server immediately shuts down after startup or when clients lose network connections, the error log provides you with valuable information to debug the problem. If you are unable to connect to SQL Server with the Enterprise manager, you can use the Windows Notepad application to open and view the error log.
Using the Event Viewer
SQL Server also logs information and error messages to the Windows NT event log. NT uses the event log NT as a repository for the operating system and applications to log informational and error messages. The Windows NT Event Viewer is located in the Windows NT Administrative Tools group. The advantage of using the Event Viewer over the error log is that errors are easy to spot because NT highlights all error messages with a red stop sign; it highlights information messages with a blue exclamation mark.
To view the detailed error message description, severity level, and state, double-click the line item. An Event Details dialog box appears. The Event Viewer also provides a search utility that enables you to search for specific types of events in the event log. For example, you can search for all the error messages in the event log.
Of course, the Windows NT Event log is only available with SQL Server 2000 running on NT and Windows 2000 platforms and is not available with Windows 9x.
You should now know where to search for SQL Server error messages as well as understand the format and meaning of SQL Server error messages. This article provides the foundation for your understanding how to interpret and research the error messages you receive during routine maintenance.
About the Authors
Mark Spenik and Orryn Sledge are authors of Microsoft SQL Server 2000 DBA Survival Guide (Click to buy) a book published by Sams Publishing. This article is based on information from their book.
Mark Spenikis the vice president of Enterprise Technologies at Trilogy Consulting located in Richmond, VA. Mark is an MCSD and is frequently invited to speak at various developer conferences and seminars.
Orryn Sledge is an MCSD as well as the Practice Director with FullTilt in Pittsburgh, PA. He has been actively involved with SQL Server since 1992 and also is a frequent speaker at various Microsoft conferences and presentations.
© Copyright Sams Publishing, All Rights Reserved