Search multiple SQL Server Error Logs at the same time

By: Mike Eastland   |   Updated: 2014-01-10   |   Comments (5)   |   Related: > Monitoring

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.

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.

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.

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

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.

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

Sample results from a test server are listed below.

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.

About the author

Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips