Search multiple SQL Server Error Logs at the same time

Problem

I am troubleshooting a specific error that recurs in my SQL Server error log. I want to identify all of the occurrences of this error from a certain point in time, but my error log has been cycled multiple times since then. How can I retrieve this information without manually opening and reviewing each error log?

Solution

The code in this tip will show how to use two system stored procedures to identify all iterations of a given string in the available SQL Server error logs since a specific point in time.

Disclaimer

Each section of code was designed to be executed as a combined, single unit. It is logically broken down for clarification. In addition, the code has been tested against the following versions: SQL Server 2005, SQL Server 2008R2, and SQL Server 2012.

T-SQL Code to Read All SQL Server Error Logs

Declare variables used in the script.

SET NOCOUNT ON

DECLARE @maxLog       INT,
        @searchStr   VARCHAR(256),
        @startDate   DATETIME;

DECLARE @errorLogs   TABLE (
   LogID   INT,
   LogDate DATETIME,
   LogSize BIGINT   );

DECLARE @logData      TABLE (
   LogDate   DATETIME,
   ProcInfo   VARCHAR(64),
   LogText   VARCHAR(MAX)   );

Initialize the variables. Whenever a SQL Server instance starts, the server process id is written to the instance error log. In this example, we will search all available SQL Server error logs for entries containing the string Server process ID is that have occurred since 10/01/2013 at 8am. In effect, we are searching for all instance start-ups that have occurred since @startDate.

SELECT @searchStr = ‘Server process ID is’,
@startDate = ‘2013-10-01 08:00’;

Use the system stored procedure sp_enumerrorlogs to populate the first table variable with the list of available error logs. Note that the procedure returns the log (archive) number, date it was created, and the size. For our purposes, we are concerned with the log number and create date.

INSERT INTO @errorLogs
EXEC sys.sp_enumerrorlogs;

Determine the oldest log that will be included in the search.

SELECT TOP 1 @maxLog = LogID
FROM @errorLogs
WHERE [LogDate]
<= @startDate
ORDER BY [LogDate] DESC;

Loop through the available error logs and use the system stored procedure sp_readerrorlog to populate the second table variable with error entries containing the search string from each log fitting the date criteria. For more details on sp_readerrorlog and xp_readerrorlog, review Tip 1476.

WHILE @maxLog >= 0
BEGIN
   INSERT INTO @logData
   EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr;

   SET @maxLog = @maxLog – 1;
END

Finally, return the error log entries matching the date and search-string criteria.

SELECT [LogDate], [LogText]
FROM @logData
WHERE [LogDate]
>= @startDate
ORDER BY [LogDate];

Sample results from a test server are listed below.

LogDate	LogText
2013-10-01 10:44:13.290	Server process ID is 5884.
2013-10-29 11:42:30.520	Server process ID is 11228.
2013-10-30 09:16:52.410	Server process ID is 11636.
2013-11-01 14:59:51.780	Server process ID is 8428.
2013-11-13 13:55:36.070	Server process ID is 4012.
2013-12-03 10:10:19.660	Server process ID is 10360.
2013-12-04 15:48:07.830	Server process ID is 7600.

Conclusion

The code in this tip can be used to programmatically identify SQL Server errors across multiple available log files. This can beneficial when the exact time an error occurred is not known, or when comparing error trends before and after making system changes to address the error(s) in question.

Complete Code Listing

Here is a complete listing of the code. Just copy and paste and change the two parameters @searchStr and @searchDate.

SET NOCOUNT ON
DECLARE @maxLog      INT,
        @searchStr   VARCHAR(256),
        @startDate   DATETIME;
DECLARE @errorLogs   TABLE (
    LogID    INT,
    LogDate  DATETIME,
    LogSize  BIGINT   );
DECLARE @logData      TABLE (
    LogDate     DATETIME,
    ProcInfo    VARCHAR(64),
    LogText     VARCHAR(MAX)   );
SELECT  @searchStr = 'Server process ID is',
        @startDate = '2013-10-01 08:00';
INSERT INTO @errorLogs
EXEC sys.sp_enumerrorlogs;
SELECT TOP 1 @maxLog = LogID
FROM @errorLogs
WHERE [LogDate] <= @startDate
ORDER BY [LogDate] DESC;
WHILE @maxLog >= 0
BEGIN
    INSERT INTO @logData
    EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr;
    
    SET @maxLog = @maxLog - 1;
END
SELECT [LogDate], [LogText]
FROM @logData
WHERE [LogDate] >= @startDate
ORDER BY [LogDate];

Next Steps

Update the retention and periodically cycle your error logs using Tip 1155 as a reference.
If your troubleshooting efforts involve more than one server, use the method shown in Tip 2086 to search the error logs for multiple SQL Server instances simultaneously.
For information on how to convert this code into a stored procedure, review the SQL Server Stored Procedure Tutorial.
Check out other MSSQLTips related to error logs.