Querying SQL Server Event Logs to search for DBCC CheckDB output

This is a long-winded response to a query raised on AskSSC, about how to report on the running of DBCC.

One way suggested was to use Ola Hallengren’s scripts to schedule and report. However, I have another way that I use that I wanted to share. Unfortunately, it isn’t particularly efficient – so I only run it once a day on each server. The main idea was taken from David Bird’s SQLOverview series of articles. I’ve done some tidying up of the date handling in my own scripts, and will show how to get at the DBCC-related stuff.

Getting SQL Server Event Logs

First step: What/Where are the Logs?

master.dbo.xp_enumerrorlogs – returns a table containing three columns: Archive number, the date the log was created, and the file size. Capture that information into a temporary table that you can then process.

CREATE TABLE #ErrorLogFiles (FileID INT, ModifiedDate VARCHAR(25) NOT NULL, FileSize bigint)
INSERT INTO #ErrorLogFiles
    EXEC MASTER.dbo.xp_enumerrorlogs
SELECT *
FROM #ErrorLogFiles

The results look something like:

(Not one of the busiest servers around, granted…)

Second Step: Get the logs

master.dbo.xp_readerrorlog @FileID – pulls the information from the ID’d error log file into a record set that you can then examine. The downside here (from my perspective) is that the record set has different schemas between versions of SQL Server – SQL Server 2000 lumps all the useful data (including event date) into a single column, but the MessageText is held as a 250-odd character string across potentially multiple rows; SQL Server 2005, 2008, 2008R2 puts it into different columns but with a longer MessageText field, meaning some slicing & dicing is required. David Bird did some of this in his script, and I’ve extended it. I’ll leave this as an exercise, by the end of which you should have a table “ErrorLog” that contains (at least) the following fields:

• Message Date
• SPID
• Message Text

Oh, OK then. Replace (in Bird’s query) the part that populates the ErrorLog table with:

IF @VersionID=8
  BEGIN
INSERT INTO tempdb.dbo.ErrorLog ([Server], [dtMessage], [SPID], [vchMessage], [ID])
    SELECT CONVERT(NVARCHAR(128),SERVERPROPERTY('Servername')) AS Server,
        CASE ISDATE( LEFT(vchMessage, 22))
            WHEN 1 THEN LEFT(vchMessage, 22)
            ELSE '1900-01-01'
        END,
        CASE ISDATE(LEFT(vchMessage, 22)) WHEN 1 THEN CASE WHEN LEN(vchMessage)>34 THEN  SUBSTRING(vchMessage, 24, 10)
            ELSE NULL END
ELSE NULL END,
        CASE ISDATE(LEFT(vchMessage, 22)) WHEN 1 THEN CASE WHEN LEN(vchMessage)>34 THEN SUBSTRING(vchMessage, 34, DATALENGTH(vchMessage)-34)
            ELSE vchMessage END
ELSE vchMessage END,
        ID
    FROM   #Errors8
  END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ErrorLog ([Server], [dtMessage], [SPID], [vchMessage], [ID])
    SELECT CONVERT(NVARCHAR(128),SERVERPROPERTY('Servername')) AS Server, LogDate, ProcessInfo, vchMessage, NULL
    FROM #Errors9
    WHERE LogDate >= @ExtractDate
  END

Third Step: Search the results

Now we’ve got the SQL Server Event Logs in a single table, we can look at the output. Fortunately, for this example, the output from DBCC CheckDB begins with “CHECKDB” in older versions, and “DBCC CHECKDB” in newer versions. Easy enough to check for, anyway. Unfortunately, the message indicating success is also garbled – “found 0 errors and repaired 0 errors” plays “finished without errors” in older versions, making the query a little more complicated if you want to check only for problems. Try this:

SELECT *
    FROM ErrorLog
WHERE ( (MessageText LIKE 'CHECKDB%')
OR (MessageText LIKE 'DBCC CHECKDB%') )
AND MessageText NOT LIKE '%found 0 errors%'
    AND MessageText NOT LIKE '%finished without errors%'
ORDER BY MessageDate DESC

About these ads