SQL Server Script to Search Through All Error Logs
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.
My solution involves creating a stored procedure in the SQL Server master database, called dbo.usp_SearchAllLogs.
The procedure takes two parameters:
- @LogType: 1 for SQL Server log and 2 for SQL Agent log
- @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):
- 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
View all my tips