Read ALL SQL Server Error Logs from Date X
Playing around with the Restore script (You can find it Restore Duration Time For A Database) I wanted to read in more than just the latest errorlog, as I set all our errorlogs to keep 99, reading all 99 log files would be rather silly if I just want to know what happened yesterday, so I built the below script (Based strongly off Rodney Landrum Simple-Talk post The DBA Script Tumb script “Read all Error Logs script” here so most of the props belongs to him!)
I think its rather handy, so thought I would share.
/* Query - Read Errorlog Files FROM X Based on Rodney Landrum Script : https://www.simple-talk.com/sql/database-administration/the-dba-script-thumb/ Set @ErrorLogStart to Datetime you want to read the errorlog from */ DECLARE @TSQL NVARCHAR(2000) DECLARE @lC INT DECLARE @ErrorLogStart DATETIME DECLARE @CurrentLogStart DATETIME SET @CurrentLogStart = GETDATE() -- SET TO DATETIME YOU WANT TO READ FROM SET @ErrorLogStart = '2014/10/01' CREATE TABLE #TempLog ( LogDate DATETIME , ProcessInfo NVARCHAR(50) , [Text] NVARCHAR(MAX) ) CREATE TABLE #logF ( ArchiveNumber INT , LogDate DATETIME , LogSize INT ) INSERT INTO #logF EXEC sp_enumerrorlogs SELECT @lC = MIN(ArchiveNumber) FROM #logF WHILE @lC IS NOT NULL BEGIN IF EXISTS ( SELECT 1 FROM #TempLog ) BEGIN SET @CurrentLogStart = ( SELECT TOP ( 1 ) LogDate FROM #TempLog ORDER BY LogDate ) END IF ( @CurrentLogStart > @ErrorLogStart ) BEGIN INSERT INTO #TempLog EXEC sp_readerrorlog @lC SELECT @lC = MIN(ArchiveNumber) FROM #logF WHERE ArchiveNumber > @lC END ELSE BEGIN BREAK END END -- Read EVERYTHING! SELECT LogDate , ProcessInfo , Text FROM #TempLog ORDER BY LogDate -- Clean up DROP TABLE #TempLog DROP TABLE #logF