View Error Logs

SQL Server Books Online (BOL) doesn't document a way to view error logs from your application. But two undocumented extended stored procedures in SQL Server 2000 and 7.0—sp_enumerrorlogs and sp_readerrorlog—let you examine error logs so that you can quickly find and correct errors.

Sp_enumerrorlogs (which you can also write as xp_enumerrorlogs—either version of the name is correct) returns the current error-log information, which Table 1, page 14, describes. For example, when you run a statement such as

EXEC master..sp_enumerrorlogs

you get a result set like the one that Figure 1, page 14, shows.

The extended stored procedure sp_readerrorlog (which you can also write as xp_readerrorlog) provides the contents of a specified error log, as Table 2 describes. In the syntax

sp_readerrorlog [n]

n is the number of the archive for the current error log (the default number is 0). When you run a statement such as

EXEC master..sp_readerrorlog 1

you get a result set like the one that Figure 2 shows. The first four rows are formatted differently than the rest of the rows, and the format of the last column (ContinuationRow) doesn't help you understand that all the returned rows are one record. The first 22 characters in each row provide date and time information. The next 10 characters in each row show the source of the event—either the Server Process ID (SPID) or the inner server process. The rest of the ERRORLOG.1 column is the text of the message.

When you're developing automatic applications that work 24 * 7, you need a way to determine which errors require immediate action. Using these stored procedures to analyze error logs can help you launch an error-recovery procedure quickly.

—Oleg G. Dantchenko
[email protected]