Check for the last clean DBCC CHECKDB in SQL Server

programming-transactsql

A common maintenance task is running DBCC CHECKDB to verify that the data stored in a SQL Server database is clean.  If possible, all of the system and user databases (except TempDB) should be checked regularly.

How do you know the last time DBCC CHECKDB was executed successfully against your database? The following script will help:

USE Master
GO
DBCC TRACEON (3604)
GO
DECLARE @DBCCPage AS TABLE
(
    DatabaseName    SYSNAME NULL,
    ParentObject    SYSNAME,
    [Object]        SYSNAME,
    Field           SYSNAME,
    [Value]         SQL_VARIANT
)
DECLARE DatabaseCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT name 
    FROM sys.databases 
    WHERE state_desc = ‘ONLINE’
OPEN DatabaseCursor
DECLARE @DatabaseName SYSNAME
FETCH NEXT FROM DatabaseCursor 
           INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
   BEGIN
      –Insert the DBCC PAGE output into the @DBCCPage table
      INSERT INTO @DBCCPage (ParentObject, [Object], Field, Value)
      EXEC(‘DBCC PAGE ([' + @DatabaseName +'], 1, 9, 3) 
            WITH TABLERESULTS’)
      –Set the database name
      UPDATE @DBCCPage SET DatabaseName = @DatabaseName 
                       WHERE DatabaseName IS NULL
      FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
   END

SELECT DISTINCT DatabaseName,
       CONVERT(DATETIME, Value) AS LastGoodDBCC,
       DATEDIFF(day, CONVERT(DATETIME, Value), 
                GETDATE()) AS DaysSinceLastGoodDBCC
FROM   @DBCCPage
WHERE  Field IN (‘dbi_dbccLastKnownGood’)
ORDER BY DatabaseName
GO
DBCC TRACEOFF (3604)
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s