Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Search multiple SQL Server Error Logs at the same time


By:   |   Updated: 2014-01-10   |   Comments (5)   |   Related: More > 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.

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.


Last Updated: 2014-01-10


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 Mike,

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

Cheers 


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

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.

SET NOCOUNT ON

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

SET  @searchStr = 'error'

DECLARE @errorLogs TABLE (LogID INT,LogDate DATETIME,LogSize BIGINT);
DECLARE @logData  TABLE (LogDate DATETIME,ProcInfo VARCHAR(64),LogText VARCHAR(MAX));
DECLARE @findOldest TABLE (LogDate DATETIME,ProcInfo VARCHAR(64),LogText VARCHAR(MAX));

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

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


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

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 http://support.microsoft.com/kb/887165, 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

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


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

Very Useful post. Thank you !!


Learn more about SQL Server tools