Verify Last successful CHECKDB on all databases

  • Comments posted to this topic are about the item Verify Last successful CHECKDB on all databases

  • Kyle,

    I use this procedure as part of my dbhealth check.

    For newly created databases (with no checkdb done) I found that the value for dbi_dbccLastKnownGood is the same as the value for the model database.

    In a way this is correct, because a new database is a copy of the model database. On the other hand the checkdb is not executed on the new database itself and therefore a healthy database is not guaranteed

    What is your opinion about this?

    regards, Robbert

  • Glad to hear someone else is getting good use out of this.

    I've noticed a similar behavior with restored databases. I've never let that concern me. However, you could make it a policy to always do a checkdb immediately after a create database statement. The db will be small, so it should finish in a few seconds.

    As for a more precise monitoring tool, you could bring the create_date from the sys.databases table into the query. You could then invalidate the false positive if the create_date was after the checkdb date:

    SELECT DatabaseName,

    CAST(Value AS datetime) AS LastGoodCheckDB,

    DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB,

    DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDB,

    db.create_date

    FROM #DBInfo_LastKnownGoodCheckDB ckdb

    INNER JOIN sys.databases db ON ckdb.DatabaseName = db.name

    ORDER BY DatabaseName

    Although I don't know every action that modifies the create_date column, I do know that sp_renamedb and alter database modify file do. If you use these functions, expect a new problem of false negatives.

    Hope that helps!

    Kyle

  • I really like this script but added the print output to allow me to execute a checkdb physical_only on all of the databases.

    /*

    Author: Kyle Neier

    Date: 3/17/2008

    Description: Examines the "boot page" of each database to

    express when the last successful CheckDB was performed

    */

    SET NOCOUNT ON

    CREATE TABLE #DBInfo_LastKnownGoodCheckDB

    (

    ParentObject varchar(1000) NULL,

    Object varchar(1000) NULL,

    Field varchar(1000) NULL,

    Value varchar(1000) NULL,

    DatabaseName varchar(1000) NULL

    )

    DECLARE csrDatabases CURSOR FAST_FORWARD LOCAL FOR

    SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')

    OPEN csrDatabases

    DECLARE

    @DatabaseName varchar(1000),

    @SQL varchar(8000)

    FETCH NEXT FROM csrDatabases INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Create dynamic SQL to be inserted into temp table

    SET @SQL = 'DBCC DBINFO (' + CHAR(39) + @DatabaseName + CHAR(39) + ') WITH TABLERESULTS'

    --Create Statement to execute a physical only checkdb

    print '-------------------------------------------------------------------

    dbcc checkdb(' + char(39) + @DatabaseName + char(39) + ') with physical_only;

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

    /*'

    --Insert the results of the DBCC DBINFO command into the temp table

    INSERT INTO #DBInfo_LastKnownGoodCheckDB

    (ParentObject, Object, Field, Value) EXEC(@SQL)

    --Set the database name where it has yet to be set

    UPDATE #DBInfo_LastKnownGoodCheckDB

    SET DatabaseName = @DatabaseName

    WHERE DatabaseName IS NULL

    print '*/'

    FETCH NEXT FROM csrDatabases INTO @DatabaseName

    END

    --Get rid of the rows that I don't care about

    DELETE FROM #DBInfo_LastKnownGoodCheckDB

    WHERE Field <> 'dbi_dbccLastKnownGood'

    SELECT

    DatabaseName,

    CAST(Value AS datetime) AS LastGoodCheckDB,

    DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB,

    DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDB

    FROM #DBInfo_LastKnownGoodCheckDB

    ORDER BY DatabaseName

    DROP TABLE #DBInfo_LastKnownGoodCheckDB


    Thanks,

    ~DH

  • Thanks for the script.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply