4

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
1
8

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

enter image description here

You can get detailed information like this

enter image description here

and check a whole bunch of servers like this

enter image description here

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

3
  • Will this run with all versions of Powershell and/or SQL Powershell?
    – John K. N.
    Mar 23 '17 at 13:44
  • +1 Rob .. Was going to edit my answer and I saw you posted. dbatools is the way for modern SQL Server administration !
    – Kin Shah
    Mar 23 '17 at 13:56
  • For the client machine (The one running the PowerShell) V3 + is supported but V5 is recommended. It will also need either SSMS 2008 or above or the SMO installed. This command will work against Servers from SQL 2005 to SQL vNext including SQL vNext on Linux. In general, where possible dbatools supports SQL2000 to SQL vNext Mar 24 '17 at 10:17
7

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.

4

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) :

enter image description here

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.

    enter image description here

2
  • Will this run in all versions of SQL Powershell or with all versions of SMO?
    – John K. N.
    Mar 23 '17 at 13:43
  • Yes all PS versions. Hopefully you are not running 1.0 :-) I would recommend @MrRob 's answer since he (and other PS gurus) are behind the awesome dbatools.io
    – Kin Shah
    Mar 23 '17 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.