Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

SQL Server Script to Search Through All Error Logs


By:   |   Read Comments   |   Related Tips: More > 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):

query output
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.


Last Update:


next webcast button


next tip button



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

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.



    



Learn more about SQL Server tools