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