Monday, January 21, 2013

Query the SQL Server Error Logs

Part of my standard work as a DBA is keeping tabs on the various logs, whether it be the Windows Event log or either of SQL Server's error/agent logs. Below is a handy script I use so I can further filter the results of sp_readerrorlog while preserving related, relevant errors as well. As is the case with any repetitive activity, automating this by creating and subscribing to an SSRS report on a weekly basis is highly recommended. Another option is to set up a Central Management Server and run the query against a number of instances - saves you from creating one report for each instance.



IF OBJECT_ID(N'tempdb..#ERRLOG') IS NOT NULL 
    BEGIN
        DROP TABLE [#ERRLOG]
    END
CREATE TABLE #ERRLOG
    (
     LogDate DATETIME
    ,ProcessInfo NVARCHAR(512)
    ,[Text] NVARCHAR(4000)
    )

INSERT  INTO [#ERRLOG]
        ( 
         [LogDate]
        ,[ProcessInfo]
        ,[Text] 
        )
        EXEC [master].sys.[sp_readerrorlog] @p1 = 0,@p2 = 1,@p3 = NULL,
            @p4 = NULL

CREATE CLUSTERED INDEX IX_#ERRLOG_LogDate ON [#ERRLOG] ( LogDate )

SELECT  e2.[LogDate]
       ,e2.[ProcessInfo]
       ,e2.[Text]
FROM    [#ERRLOG] AS e
        LEFT JOIN [#ERRLOG] AS e2 ON e2.LogDate = e.[LogDate]
                                     AND e2.ProcessInfo = e.[ProcessInfo]
WHERE   e.[Text] LIKE N'%ERROR:%'
        AND DATEDIFF(d,e2.LogDate,CURRENT_TIMESTAMP) < 2
ORDER BY e2.[LogDate] DESC

IF OBJECT_ID(N'tempdb..#ERRLOG') IS NOT NULL 
    BEGIN
        DROP TABLE [#ERRLOG]
    END