Error Logs Part II -- Enumerating the error logs

In my first post on SQL Server Error Logs, I briefly mentioned using xp_enumerrorlogs to list the archived error logs. Here I want to demonstrate how to use the procedure to find and output all error logs since a specific date.

xp_enumerrorlogs

This procedure returns 3 columns: Archive #, Date, and Log File Size (Byte). Archive numbering is 0 based with 0 being the currently active log file. As a log file is archived, the number increases; 0 becomes 1, 1 becomes 2, and so on. The output is ordered alphabetically by the Archive #.

 Finding which archive to start with is easy. You just find the newest file that is older than the date for which you are searching. The one catch here, is that if your date is beyond the range of the archives, your query will not find an archive. As a sanity check, if no archive is found beyond the range of my search date, I simply choose the oldest file available.

From there, it's a simple looping process to read in each file starting with the highest archive number and continuing down to 0, the currently active number.

The Script: ErrorLogsSinceDate

Declare @OldestLog datetime,
    @FirstLog int
Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
                        LogDate datetime null,
                        ProcessInfo nvarchar(100) null,
                        LogText nvarchar(max) null)
Declare @EnumLogs Table (ArchiveNum int not null primary key,
                        ArcDate Datetime not null,
                        LogFileSize bigint not null)

Set @OldestLog = '12/31/2007'

Insert Into @EnumLogs
Exec master..xp_enumerrorlogs

Select Top 1 @FirstLog = ArchiveNum
From @EnumLogs
Where ArcDate < @OldestLog
Order By ArcDate Desc

If @FirstLog Is Null
  Begin
        Select Top 1 @FirstLog = ArchiveNum
        From @EnumLogs
        Order By ArchiveNum Desc
  End

While @FirstLog >= 0
  Begin
        Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
        Exec master..xp_readerrorlog @FirstLog

        Set @FirstLog = @FirstLog - 1
  End

Select *
From @ErrorLog
Order By LogID Desc

No, I'm not going to keep making corny references to war and being a soldier. But in many ways, we dba's are soldiers in a war against poor performance. Often it can be a struggle to get the information and resources you need, but that doesn't lessen the expectation that we can deliver speedy results.

I hope this first article will help a few people resolve issues a little bit quicker by helping them search their error logs.

I intend to follow up this article with a more advaned look at the error logs and how to use it to search for hidden problems before they become public.