info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL - Query all SQL Server Error Log Files
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-11-09









T-SQL:


IF OBJECT_ID('tempdb.dbo.#SSLogsLoad', 'U') IS NOT NULL DROP TABLE #SSLogsLoad;

DECLARE @SSLogsTotal TABLE (ArchiveNo INT, LogDate DATETIME, LogFileSize INT)
INSERT INTO @SSLogsTotal Exec xp_enumerrorlogs
--SELECT ArchiveNo, LogDate, LogFileSize AS 'Error LogFileSize (B)' FROM @SSLogsTotal

DECLARE @Cnt int
Set @Cnt = (Select count(*) from @SSLogsTotal)
Set @Cnt = @Cnt - 1 --Since most recent Log File is "0"

DECLARE @SQL nvarchar(max)

CREATE TABLE #SSLogsLoad (LogDate DATETIME, [Source] VARCHAR(100), [Message] VARCHAR(MAX))

While @Cnt >= 0 Begin
Set @SQL = 'INSERT INTO #SSLogsLoad ([LogDate], [Source], [Message]) EXEC xp_readerrorlog ' + Convert(nvarchar(10),@Cnt)
Exec sp_executesql @SQL
Set @Cnt = @Cnt - 1
End

SELECT
[LogDate],
[Source],
[Message]
FROM #SSLogsLoad
WHERE
[Message] LIKE '%Always On%'
ORDER BY
LogDate DESC