SQL Server DBCC CHECKDB Last Execution Date for All Databases

Introduction

Using DBCC CHECKDB Command one can check the logical & physical integrity of all the objects within the specified database. As a Database Administrator once must run DBCC CHECKDB on all the SQL Server Databases to assure that you don’t have any corruption issues within the databases which you manage.

However, there is no easy way to identify when last time DBCC CHECKDB was run successfully on all the databases. The TSQL script mentioned in this article uses the undocumented DBCC DBINFO command to read database boot page to check when last time DBCC CHECKDB command was successfully run against the user database.

The TSQL script mentioned in this article can be run against SQL Server 2005 and higher versions to Identify Last Successful DBCC CHECKDB Run Date for all Databases in SQL Server.

Use Master
GO

EXEC dbo.LastKnownGoodCheckDBDateForAllDBs
GO

 

Below mentioned is the TSQL Script to Create LastKnownGoodCheckDBDateForAllDBs Stored Procedure to "Find when Last Time DBCC CHECKDB Command was run Successful on all the Databases in SQL Server".

Use master
GO

IF OBJECT_ID ( 'dbo.LastKnownGoodCheckDBDateForAllDBs', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.LastKnownGoodCheckDBDateForAllDBs;
GO

CREATE Procedure dbo.LastKnownGoodCheckDBDateForAllDBs
AS

BEGIN

CREATE TABLE #DBInfoResults
(
[ParentObject] VARCHAR(512),
[Object] VARCHAR(512),
[Field] VARCHAR(512),
[VALUE] VARCHAR(512)
)

CREATE TABLE #FindDBCCLastKnownGoodDate
(
[DatabaseName] SYSNAME,
[LastKnowGoodDBCCCheckDate] NVARCHAR(512),
)

DECLARE
@DatabaseName SYSNAME,
@ExecTSQL VARCHAR(512);

DECLARE cDBInfoResults CURSOR FOR

SELECT NAME
FROM sys.databases
WHERE STATE_DESC = 'ONLINE'

OPEN cDBInfoResults;

FETCH NEXT FROM cDBInfoResults INTO @DatabaseName;

WHILE @@Fetch_Status = 0

BEGIN

SET @ExecTSQL = 'Use [' + @DatabaseName +'];' + CHAR(10)+ CHAR(13) +
'DBCC DBInfo() WITH TABLERESULTS, NO_INFOMSGS;' + CHAR(10)+ CHAR(13)

INSERT INTO #DBInfoResults
EXECUTE (@ExecTSQL);

INSERT INTO #FindDBCCLastKnownGoodDate
(DatabaseName, [LastKnowGoodDBCCCheckDate])

SELECT @DatabaseName, VALUE
FROM #DBInfoResults where Field = 'dbi_dbccLastKnownGood'

TRUNCATE TABLE #DBInfoResults;

FETCH NEXT FROM cDBInfoResults INTO @DatabaseName;

END

CLOSE cDBInfoResults;
DEALLOCATE cDBInfoResults;

SELECT
 DISTINCT([DatabaseName]) AS [Database Name]
,[LastKnowGoodDBCCCheckDate] AS [Last Know Good DBCC Check Date]
FROM #FindDBCCLastKnownGoodDate
ORDER BY [LastKnowGoodDBCCCheckDate] DESC

DROP TABLE #DBInfoResults
DROP TABLE #FindDBCCLastKnownGoodDate

END
GO

It is highly recommended to run DBCC CHECKDB periodically to identify database corruption issues.

Read more: http://www.mytechmantra.com/LearnSQLServer/Last-Successful-DBCC-CHECKDB-Run-Date-for-all-Databases-in-SQL-Server/#ixzz3h4ftEIJu
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook