See Also: Main_Page - Database Administration - Troubleshooting SQL Server

SQL Server Error Logs Overview

As a DBA you will detect the majority of SQL Server errors through the SQL Server error log. You should examine the error log daily to ensure that all potential issues are investigated as quickly as possible. You can read the log through Enterprise Manager or by executing the xp_readerrorlog extended stored procedure. SQL Server also supplies the sp_readerrorlog system procedure which in turn calls xp_readerrorlog.

Tutorial Video about Checking Error Logs

In this three minute video, SQLServerPedia Editor-in-Chief Brent Ozar explains where he looks first when he's troubleshooting a SQL Server problem.

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090409-ErrorLogs.flv

For more tutorial videos like this, check out the SQL Server Tutorials page.

More About SQL Server Error Logs

By default, the SQL Server error log is stored in the Program Files\Microsoft SQL Server\MSSQL\Log directory. The most current error log file is called ERRORLOG. If you stop and re-start SQL Server, the old log will be archived and a new one will be created. In addition, you can re-cycle the error log by executing the DBCC ERRORLOG command or the sp_cycle_errorlog system procedure.

By default, SQL Server will keep six archived error log files along with the error log that is currently used. However, you can override this number if you wish to archive fewer or more logs. This can be accomplished by right clicking on SQL Server Logs within Enterprise Manager and choosing "Configure".

Enterprise Manager will let you read the current or archived error logs. Alternatively, you can use xp_readerrorlog to read any of the available logs. This procedure returns the contents of the current error log if executed without a parameter. Optionally you can specify an integer between 1 and the maximum number of logs to read any of the archived logs, as in the following:

EXEC xp_readerrorlog 3

Results (abbreviated):

ERRORLOG.3                                                                            ContinuationRow  
2003-10-08 20:39:48.13 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)    0   Dec 17 2002 14:22:05                                                                 0   Copyright (c) 1988-2003 Microsoft Corporation                                        0   Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)                      0  
2003-10-08 20:39:48.16 server    Copyright (C) 1988-2002 Microsoft Corporation.       0  
2003-10-08 20:39:48.16 server    All rights reserved.                                 0  
2003-10-08 20:39:48.16 server    Server Process ID is 656.                            0  
2003-10-08 20:39:48.16 server    Logging SQL Server messages in file 'E:\Program                                    Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.      0  
2003-10-08 20:39:48.28 server    SQL Server is starting at priority class 'normal'                                   (1 CPU detected).                                    0  
2003-10-08 20:39:50.72 server    SQL Server configured for thread mode processing.    0  
2003-10-08 20:39:50.78 server    Using dynamic lock allocation. [2500] Lock Blocks,                                   [5000] Lock Owner Blocks.                            0  
2003-10-08 20:39:50.89 server    Attempting to initialize Distributed Transaction                                   Coordinator.                                         0  
2003-10-08 20:40:31.42 spid3     Recovery complete.                                   0  
2003-10-08 20:40:31.42 spid3     SQL global counter collection task is created.       0  
2003-10-08 20:40:31.71 spid3     Launched startup procedure 'sp_MSrepl_startup'       0  
2003-10-08 20:40:46.51 spid51    Using 'xpsqlbot.dll' version '2000.80.194' to                                    execute extended stored procedure 'xp_qv'.           0  
2003-10-08 20:50:02.36 spid54    Using 'xpstar.dll' version '2000.80.760' to execute                                   extended stored procedure 'xp_instance_regread'.     0  
2003-10-08 20:50:02.36 spid53    Using 'xpstar.dll' version '2000.80.760' to execute                                   extended stored procedure 'xp_instance_regread'.     0  
2003-10-09 00:00:06.68 backup    Log backed up: Database: northwind, creation                                    date(time): 2001/05/10(12:10:06), first LSN:                                    44:244:1, last LSN: 65:319:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK:{'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\northwind_tlog_200310090000.TRN'}).                                 1  
2003-10-09 14:47:45.62 server    SQL Server terminating because of system shutdown.   0

As you can tell from this short output the first column contains the actual contents of the error log, whereas the second column (Continuation Row) shows whether the current row is a continuation of the previous row. If continuation row has a value of 1 that means the previous entry did not fit on a single row.

The third alternative for reading error logs is simply opening them up with a text editor, such as NOTEPAD. Viewing error logs allows you to see the server process ID (SPID) of the connection that generated the error, which can be helpful for further investigation.

SQL Server error logs record system startup and configuration information, the status of recovering each system and user-defined database, any changes in configuration options, KILL statements, deadlock conditions, database and transaction log backups and more. Reading every single log record can be rather time-consuming. Typically you should ignore messages similar to the following:

Some other messages require immediate attention, however. For example, suppose you see the following error repeatedly: "Starting up database 'sample'." This means the database configuration option "auto-close" has been turned on. Auto-close should never be turned on for any production databases; therefore, you should turn this option off using the sp_configure system procedure.

Suppose you cannot start the SQL Server service and you see the following error in the log: "initconfig: Error 32(The process cannot access the file because it is being used by another process.) opening 'C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf' for configuration information.". This means that the master.mdf file, which happens to be the primary data file for the master database is being used by another process, such as a tape backup software. You won't be able to start SQL Server until the backup software releases its lock on master.mdf. Sometimes a Windows process has trouble releasing a lock on a particular file. If that happens, you will have to reboot the server.

SQL Server recovers every database at each startup, saving committed transactions to disk and rolling back uncommitted transactions. The error log will reflect the recovery process - typically you will see series of messages reflecting the recovery status of "XYZ" database. Occasionally you will see an error stating that the database cannot be recovered and it is marked suspect. Generally the suspect status means that the database cannot be recovered, however the reasons for marking a database as suspect can vary. For example, if you lose the transaction log file due to failed disk (or by deleting it), the database could be marked as suspect the next time SQL Server is started. Another time when a database is marked suspect is when the disk drive where the data files reside runs out of space. SQL Server cannot roll committed transactions forward because it has no room on the disk. If this happens you should take the following steps:

• Execute the sp_resetstatus system procedure. This procedure accepts database name as the single parameter. For example:

  EXEC sp_resetstatus 'NORTHWIND'

• Add a data file to the database using a different disk or existing file-group that has some space available, as in the following:

  ALTER DATABASE northwind  ADD FILE    ( NAME= 'northwind_data3',    FILENAME = 'c:\northwind_data1.mdf',    SIZE = 1)  TO FILEGROUP [user_defined]

• Stop and restart the SQL Server service. With additional room SQL Server should be able to save all committed transactions to disk.

SQL Server records numerous messages in the error log, however, some errors (those with a severity of less than 19) are by default only returned to the client and not written to the error log. You can override this default behavior if you wish to record particular messages in the error log. To modify the default behavior use the system procedure sp_altermessage. The first parameter is the message identifier from the sysmessages system table, found only in the master database. Notice that you can use sp_altermessage for system supplied messages as well as user-defined messages. The second parameter needs to be "WITH_LOG" in order to force the error to always be written to the SQL Server error log as well as Windows Application Log. The last parameter can be TRUE or FALSE, depending on how you wish to alter the message's behavior. For example, to force message 50002 to always be written to the log you would execute the following:

EXEC sp_altermessage 50002, WITH_LOG, TRUE

Every subsequent execution of error 50002 will be recorded in the error log.

In addition to reading the error log occasionally you might also have to examine the stack dump generated by SQL Server. Dump files are generated when SQL Server encounters an error that aborts a transaction and you need the background information to investigate the cause of the problem. In addition, the stack dumps will be very helpful for Microsoft's technical support if you decide to call them. The error log will contain a record informing you that dump file is generated. You are likely to see messages similar to the following:

Stack Signature for the dump is 0x2294B5BA  SQLDumpExceptionHandler: 
Process 3209 generated fatal exception c00001d  SQL Server is terminating the process

This sort of error will be followed by a huge amount of hexadecimal output along with some humanly decipherable text. Stack dump files (generally fatal errors similar to the one above will generate several files) and will be stored in SQL Server installation directory in the LOG folder. The file name will be something similar to the following: SQL00001.dmp. Within the file you will find a header like this:

================================================================
BugCheck Dump                                                            
================================================================
This file is generated by Microsoft SQL Server 8.00.194                          
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.

For each session connected to the server at the time of the error the dump file will contain the output of DBCC PSS, DBCC INPUTBUFFER and DBCC OUTPUTBUFFER; as you might imagine such output can be quite voluminous. In fact if you're curious to see the format of a dump file you can execute the DBCC STACKDUMP statement. We STRONGLY urge you to do this on a test server, NOT on production server.

As a DBA you should also examine the SQL Server Agent log. This can be accomplished through SQL Server Enterprise Manager (EM). Expand the Management folder, then right click SQL Server Agent and choose Display Error Log. You can filter the SQL Server Agent error log by error type (information, error, warning) as well as by error text.

Alternatively you can view SQL Server Agent's log using Notepad. By default SQL Server Agent's error log is stored in the LOG folder under the SQL Server installation directory. File names are similar to SQLAGENT.OUT. SQL Server keeps up to 9 SQL Server Agent log files before recycling them.

1. Backup and restore of databases and transaction logs
2. Recovery events that typically occur at SQL Server startup
3. Any failed SQL Server jobs
4. Any user-defined error message called with XP_LOGEVENT extended procedure. You can also write user-defined error messages by executing the RAISERROR statement and using the WITH LOG clause.
5. Maintenance DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC executed against any database
6. Turning trace flags on or off.
7. SQL Servers usage of a particular session for a long period of time. This is typically the connection that runs the SQL Server service.
8. Starting and stopping Profiler traces

Much of this information can be found in the SQL Server error log as well, but the Application log gives you yet another place to look for additional clues. If SQL Server is experiencing problems with starting the service, the reason might be another Windows service that has not started. Such problems are recorded in the System Log of the Event Viewer; therefore, you should monitor the System Log along with the Application Log.

The Event Viewer categorizes all events as Error, Information or Warning. Informational messages are denoted with a blue "I", warnings have a yellow triangle with an exclamation mark and errors have a red circle with a white cross inside. Double clicking individual messages within the Event Viewer will show you the details of the message. The up and down arrows let you navigate to the previous and next adjacent messages in the log. The copy button allows you to copy the contents of the message, bytes or words, depending on your selection, to the clipboard.

Informational messages can be safely ignored since they simply indicate normal activity occurring on the database server. Warning and error messages, on the other hand, should be examined and investigated. Note that the Event Viewer is not limited to logging only SQL Server messages. On the contrary, it will log messages generated by any Windows application. As shown above, SQL Server error messages will indicate MSSQLSERVER as their source. As a database administrator you should be most concerned about SQL Server messages; however, any errors generated by the operating system and any other applications running on the same server should be investigated as well.