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



Advertisements