The active SQL Server error log is one of the first places people look when there is a problem. For many people, this is a frustrating exercise. The interfaces for viewing the log in Enterprise Manager and Management Studio are slow and can not be filtered or searched.

There is a solution to that problem. You make the error logs fast and searchable by using the undocumented extended stored procedure xp_readerrorlog.

xp_readerrorlog

This undocumented extended stored procedure will output the contents of the error log one line at a time. With no parameters, it ouputs the active error log. It accepts an optional int parameter to specify the archive error logs. The higher the number passed in, the older the log file.

The following outputs the active error log:
Exec master..xp_readerrorlog

This outputs the first archived error log:
Exec master..xp_readerrorlog 1

You can make this output searchable by inserting the output into a temp table or a table variable and filtering the select from this table.

Here is a script that I use to quickly check the active SQL Server 2005 error log with the backup reports filtered out:

Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
        LogDate datetime null,
        ProcessInfo nvarchar(100) null,
        LogText nvarchar(4000) null)

Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
Exec master..xp_readerrorlog

Select *
From @ErrorLog
Where CharIndex('Backup', ProcessInfo) = 0
Order By LogID Desc

The format of the output is a little different for SQL Server 2000 plus I cannot insert the execution of a stored procedure into a table variable. I use this script for SQL Server 2000:

Create Table #ErrorLog (LogID int identity(1, 1) not null primary key,
        LogText nvarchar(4000) null,
        ContinuationRow int null)

Insert Into #ErrorLog (LogText, ContinuationRow)
Exec master..xp_readerrorlog

Select *
From #ErrorLog
Where CharIndex('Backup', LogText) = 0
Order By LogID Desc

Drop Table #ErrorLog

xp_enumerrorlogs

If you want to get fancy, you can incorporate the output of xp_enumerrorlogs into your own script. This undocumented extended stored procedure returns a list of archive files (the active file is archive # 0) with file sizes and dates.