DBCC CHECKDB LastKnownGood

Question

I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server. Is there a better way to do this?

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

CREATE TABLE #Results (ParentObject varchar(100), Object varchar(100), Field varchar(100), Value varchar(100))
GO

INSERT INTO #Results
EXEC sp_msForEachdb @command1 = 'DBCC DBINFO (''?'') WITH TABLERESULTS'

ALTER TABLE #Results ADD ID INT IDENTITY(1, 1)
GO -- required here

SELECT r.Value as [Database], r2.Value as CheckDB_LastKnownGood
FROM #Results r
INNER JOIN #Results r2
ON r2.ID = (SELECT MIN(ID) FROM #Results WHERE Field = 'dbi_dbccLastKnownGood' AND ID > r.ID)
WHERE r.Field = 'dbi_dbname'
ORDER BY r.Value

score 8 · Answer 1 · answered Mar 23 '17 at 13:34

I would use the PowerShell Command from the dbatools module Get-DbaLastGoodCheckDb which will enable you to check all of your servers in one go.

Here is a sample output from the command

You can get detailed information like this

and check a whole bunch of servers like this

It's really quick too. In my lab of 10 servers with 125 databases it completes in 5.3 seconds

You can read more about dbatools at https://dbatools.io

Hannah Vernon · Answer 2 · 2016-03-29T16:59:50.183

Instead of using sp_msForEachdb, which is problematic as Aaron described, you could use a cursor for this.

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results;
END

CREATE TABLE #Results
(
    DatabaseName SYSNAME NULL
    , IsOnline BIT NULL
    , ParentObject varchar(100) NULL
    , [Object] varchar(100) NULL
    , [Field] varchar(100) NULL
    , [Value] varchar(100) NULL
);

DECLARE @cmd NVARCHAR(MAX);
DECLARE @dbName SYSNAME;
DECLARE @IsOnline BIT;

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT DBCCCommand = 'DBCC DBINFO(''' + d.name + ''') WITH TABLERESULTS;'
    , DatabaseName = d.name
    , IsOnline = CONVERT(BIT, 
        CASE WHEN d.state_desc = 'ONLINE' THEN 1 ELSE 0 END
      )
FROM sys.databases d
ORDER BY d.name;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline;

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (@dbName, 0, 1) WITH NOWAIT;
    IF @IsOnline = 1
    BEGIN
        INSERT INTO #Results (ParentObject, [Object], [Field], [Value])
        EXEC sp_executesql @cmd;
        UPDATE #Results 
        SET DatabaseName = @dbName
            , IsOnline = @IsOnline
        WHERE DatabaseName IS NULL;
    END
    ELSE
    BEGIN
        INSERT INTO #Results (DatabaseName, IsOnline)
        VALUES (@dbName, @IsOnline)
    END

    FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline;
END

CLOSE cur;
DEALLOCATE cur;

SELECT r.DatabaseName
    , LastKnownGoodDate = CONVERT(DATETIME, r.value, 120)
    , r.IsOnline
FROM #Results r
WHERE r.field = 'dbi_dbccLastKnownGood'
    OR r.field IS NULL;

This output has the database name along with the LastKnownGood column from the DBCC DBINFO output as a DATETIME variable. Databases that are not "online" are included in the output, with the LastKnownGoodDBCC column set to NULL.

The cursor created in my example above is a local, forward-only, static cursor that is highly efficient. Local indicates the scope of the cursor is limited to our session, which is clearly not a problem (the sp_msForEachdb uses a global cursor that is quite expensive to setup). The forward-only option indicates we don't intend on being able to scroll backwards though the cursor, and allows SQL Server to use forward-only, read-only cursor semantics. The static option forces SQL Server to materialize the cursor results into a temporary table in tempdb, and read each row from there. The MSDN page on DECLARE CURSOR has excellent details about the cursor options, and is well worth reading and understanding.

score 4 · Answer 3 · answered Mar 29 '16 at 21:59

Just to add a different flavor and my quest of learning Powershell ... You can accomplish the same using below code (it has some room for improvements, but its much short, clean and you can provide a list of sql servers that you want to check against):

<#  
    .NOTES
     ===========================================================================
     Created with:  SAPIEN Technologies, Inc., PowerShell Studio 2016 v5.2.115
     Created on:    3/29/2016 
     Created by:    Kin 
     Purpose:       For dba.stackexchange.com 
     Question:      http://dba.stackexchange.com/q/133687/8783      
     Reference:     https://www.mssqltips.com/sqlservertip/2948/check-dbcc-checkdb-last-execution-using-powershell/
     ===========================================================================
    .DESCRIPTION
        -   Reads the input from the user and connects to the sql server instance 
        -   Finds the "dbi_dbccLastKnownGood" along with the date
#>
cls
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$YourSQLServerInstanceName = Read-host -prompt 'Input your sql server instance name  '
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $YourSQLServerInstanceName
foreach ($db in $serverInstance.Databases | Where-Object { ($_.IsAccessible -like "TRUE") -and ($_.Name -ne "tempdb")  })  #we ONLY want ONLINE databases and NO TEMPDB
{
    $lastDBCC_CHECKDB = $db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where { $_.Field.ToString() -eq "dbi_dbccLastKnownGood" } 
    $lastDBCC_CHECKDB | select-object `
                        @{Label='Last DBCC CHECKDB execution'; Expression =$({$db.Name})}`
                        ,@{Label='DateTime';Expression=$({$lastDBCC_CHECKDB.Value[0]})} | Get-Unique    
}

Below is the output (click to enlarge) :

Couple of things that you need to consider / be aware :

If the database is READONLY, the dbi_dbccLastKnownGood will not be updated even thought DBCC CHECKDB runs successfully.
Robert Davis blogged that DBCC CHECKFILEGROUP will update the dbi_dbccLastKnownGood along with DBCC CHECKDB.

DBCC CHECKDB LastKnownGood

3 Answers3

Linked