Search multiple SQL Server Error Logs at the same time

By:   |   Comments (5)   |   Related: > Monitoring


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?


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.


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.


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

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
    INSERT INTO @logData
    EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr;
    SET @maxLog = @maxLog - 1;

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.


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.


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

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

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Mike Eastland 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

Comments For This Article

Friday, May 10, 2019 - 4:06:32 AM - Behzad Ghanbari Back To Top (80017)


Many thanks for this. Your document helped me. We are blessed to have people like you to share their knowledge.


Tuesday, January 21, 2014 - 9:40:05 AM - webtomte Back To Top (28159)

I found that the code in the article did not show entries in the oldest archive.
So I took the liberty to change it to below code instead. Hence @startDate is removed and dynamically searched.
A search for (c) ,which occurs at restart both in the first line and second line written, proves the difference where the above code shows from archive 5 (of 6). Mine shows from #6 as well. Hope you like it.


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

SET  @searchStr = 'error'


INSERT INTO @errorLogs
EXEC sys.sp_enumerrorlogs;

SELECT TOP 1 @maxLog = LogID FROM @errorLogs ORDER BY LogDate

INSERT INTO @findOldest
 EXEC sys.sp_readerrorlog @maxLog, 1

SELECT TOP 1 @startDate = LogDate FROM @findOldest ORDER BY LogDate

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

 [LogDate] >= @startDate
 [LogDate] DESC;

Wednesday, January 15, 2014 - 9:34:54 AM - Bill Back To Top (28091)

There is a potential problem with production uses of sp_enumerrorlogs and sp_readerrorlog (and the xproc): They are not documented by Microsoft. That means Microsoft can change the results they return, or even remove them from exisitence, without any advance public notice. For production code it would be more robust to avoid their use, by replacing them with code under your control.

While those sprocs are used by SSMS and while there is a reasonable expectation that they will continue to exist, in the past Microsoft has changed an undocumented stored procedure (used by SSMS) without offering any public advance notice: xp_regread. The result of its unexpected change was, which Microsoft graciously calls a "bug", when the reality was (and still is) that Microsoft's SQL Server product documentation does not describe xp_regread's behavior. And the reason 887165 was written is that there was a community uproar, hence Microsoft felt there was a need to supply a "resoltuion" (for its undocumented behavior). The 887165 "resolution" was not offered instantaneously after SP4 was realeased - DBAs and Developers had to suffer until it was released. And the reason the community was in a uproar were web sites like this one, suggesting they be used (alweays with good intentions). Still, the history lesson offered by xp_regread's lesson is: Caveat emptor :).

Replacing their use with a text provider is one an alternative, but the format of an errorlog's text file is also not documented (and it's format is fairly irregular). As such, using sp_enumerrorlogs and sp_readerrorlog is just about as robust as a text provider, but you may need to be prepared for broken code after applying any SQL Server service pack, ***ulative update, or hotfix. Undocumented code is rather frustrating: IMO, Microsoft long ago needed to take a dump, or get off the can.

Friday, January 10, 2014 - 4:45:23 PM - Mike Back To Top (28018)

Thanks Sajal.  I'm glad you found it helpful.

Friday, January 10, 2014 - 3:10:24 PM - Sajal Bagchi Back To Top (28015)

Very Useful post. Thank you !!

get free sql tips
agree to terms