Reading all of your SQL Server Logs with one query

I want the ability to search through all the SQL Server logs without having to know how many log files there are, so I created this script to do that:

--declare a variable to hold the archive log number
DECLARE @archivenumber tinyint

/* use the appropriate drop syntax below for your version of sql server */

 --drop table syntax only works in 2016 and later 
 DROP TABLE IF EXISTS #enumerrorlogs;
 DROP TABLE IF EXISTS #errorLog;

 --drop table in older than 2016 versions 
 IF OBJECT_ID(N'tempdb..#enumerrorlogs') IS NOT NULL
 DROP TABLE #enumerrorlogs
 IF OBJECT_ID(N'tempdb..#errorLog') IS NOT NULL
 DROP TABLE #errorLog

--create temp tables to store the data 
CREATE TABLE #enumerrorlogs 
(archivenumber tinyint, 
 logdate datetime, 
 logfilesize int);

CREATE TABLE #errorLog 
 (LogDate DATETIME, 
 ProcessInfo VARCHAR(64), 
 [Text] VARCHAR(MAX));

--begin looping through rows
set rowcount 0

INSERT INTO #enumerrorlogs
EXEC sys.sp_enumerrorlogs;

set rowcount 1

select @archivenumber = archivenumber from #enumerrorlogs
 while @@rowcount <> 0
 begin
 set rowcount 0
 INSERT INTO #errorLog
 EXEC sp_readerrorlog @archivenumber
 delete #enumerrorlogs where archivenumber = @archivenumber
 print @archivenumber
 set rowcount 1
 select @archivenumber = archivenumber from #enumerrorlogs
 end

 set rowcount 0

--select from temp table to list all the log information
SELECT @@servername as ServerName, LogDate, Text 
FROM #errorLog

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.