Microsoft SQL Server 2000 Error Messages
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.
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 [ic:ccc]'d:\mssql7\data\MSSQL\log\ERRORLOG'. 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 [ic:ccc] processing 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
Page 2 of 2