An easier way to work with the SQL Server Error Log

There are three main ways to access the SQL Error Log: via the SSMS GUI, via the system stored procedure sp_readerrorlog or the extended stored procedure xp_readerrorlog. The code below I have close to hand in a Snippet and I probably use it every day. It copies the contents of the SQL error log and optionally the SQL Agent error log to a temporary table. Once there you query it for specific information. This can be also be done by using parameters 3 and 4 of sp_readerrorlog but for me this is a little more flexible.

 /*

sp_readerrorlog can take 4 optional parameters, they are:

    1. The number of the error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

    2. Log file type: 1 or NULL = error log, 2 = SQL Agent log

    3. Search string 1: String one you want to search for

    4. Search string 2: String two you want to search for to further refine the results

*/

----------------

-- Current SQL Error log

---------------

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#ErrorLog'))

    DROP TABLE #ErrorLog

CREATE TABLE #ErrorLog (

       logdate DATETIME

       ,processinfo VARCHAR(200)

       ,info VARCHAR(8000)

       )

GO

INSERT INTO #ErrorLog

EXEC sp_readerrorlog 0

       ,1

GO

CREATE CLUSTERED INDEX ix_date ON #ErrorLog (logdate)

GO

   

-- Show the last 10,000 errors within the last 24 hours

SELECT TOP 10000

    logdate

    ,processinfo

    ,info

FROM #ErrorLog

WHERE logdate > DATEADD(HOUR, - 24, GETDATE())

ORDER BY 1 DESC

-----------------

-- Current SQL Agent log

-----------------

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#ErrorLogAgent'))

    DROP TABLE #ErrorLogAgent

CREATE TABLE #ErrorLogAgent (

       logdate DATETIME

       ,processinfo VARCHAR(200)

       ,info VARCHAR(8000)

       )

GO

INSERT INTO #ErrorLogAgent

EXEC sp_readerrorlog 0

       ,2

GO

CREATE CLUSTERED INDEX ix_date ON #ErrorLogAgent (logdate)

GO

-- Show the last 10,000 errors within the last 24 hours

SELECT TOP 10000

    logdate

    ,processinfo

    ,info

FROM #ErrorLogAgent

WHERE logdate > DATEADD(HOUR, - 24, GETDATE())

ORDER BY 1 DESC