SQL Server Script to Search Through All Error Logs

By: Eli Leiba   |   Updated: 2018-07-27   |   Comments   |   Related: > Error Logs

Problem

The requirement is to create a simple SQL Server log report that will display all relevant log rows from all log files in SQL Server. These stored procedures xp_readerrorlog and sp_readerrorlog allows searching the SQL Server error logs, but only one log can be searched at a time using these procedures. The procedure in this tip searches all the available logs, by looping through each log in turn for both the SQL Server Error Logs and the SQL Server Agent Logs.

Solution

My solution involves creating a stored procedure in the SQL Server master database, called dbo.usp_SearchAllLogs.

The procedure takes two parameters:

1. @LogType: 1 for SQL Server log and 2 for SQL Agent log
2. @filterstr: The log rows filter, leave it blank for everything

The procedure uses two table variables: @LogList for the list of all log files and @ALLLogRows to accumulate the log rows from all of the log files.  The procedure loops over all the found log files (@LogList table) and inserts all the rows, filtered by the given filter, to the @ALLLogRows table.  At the end, the rows of @ALLLogRows are displayed.  Note that the contents of these temporary tables are deleted automatically when the procedure exits (since the two tables are actually variables).

T-SQL Code to Read All SQL Server Log Files

USE master
GO

-- ================================================================
-- Author: Eli Leiba
-- Create date: 07-2018
-- Procedure Name: dbo.usp_SearchAllLogs
-- Description:
--    Search for a string through all available logs where:
--    LogType = 1 (SQL Server log) 
--    LogType = 2 (SQL Agent log)
--    @filterstr is the rows filter - leave it blank for everything
-- ================================================================
CREATE PROCEDURE dbo.usp_SearchAllLogs (@LogType INT = 1, @filterstr NVARCHAR (4000) = '')
AS
BEGIN
   -- The list of all logs table
   DECLARE @LogList TABLE (LogNumber INT, StartDate DATETIME, SizeInBytes INT)
   -- All the log rows results table
   DECLARE @ALLLogRows TABLE (LogDate DATETIME, 
                              ProcessInfo NVARCHAR (4000), 
                              Test NVARCHAR (4000))
 
   -- Store in a table variable
   INSERT INTO @LogList 
   EXEC xp_enumerrorlogs @LogType
 
   -- Iterate on all the logs and collect all log rows
   DECLARE @idx INT = 0
   WHILE @idx <= (SELECT MAX (LogNumber) FROM @LogList)
   BEGIN
      INSERT INTO @ALLLogRows
      EXEC xp_readerrorlog @idx -- Log number
         , @LogType -- 1=SQL Server log, 2=SQL Agent log
         , @filterstr -- filter string
         , @filterstr
 
      SET @idx += 1
   END
   -- Return the results from the log rows table variable
   SELECT *
   FROM @ALLLogRows
   ORDER BY LogDate DESC
END
GO

Examples Stored Procedure Execution

Use master
Go
Exec dbo.usp_SearchAllLogs 1,'ERROR'
Go
Exec dbo.usp_SearchAllLogs 2,'ERROR'
Go

and the results are (on my server):

Next Steps

• You can create and compile this simple procedure in your application database and use it as a simple T-SQL tool for searching all the SQL Server and/or SQL Agent Log files for errors.
• The procedure was tested using SQL Server 2014, 2016 and 2017, but should work with all versions of SQL Server.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

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

View all my tips