http://www.sqlservercentral.com/blogs/sqlrnnr/2012/11/20/last-known-good-checkdb/

Printed 2014/07/25 09:58AM

Last Known Good CheckDB

By Jason Brimhall, 2012/11/20

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

Code block   
USE [master]
GO
 
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestB')
DROP DATABASE [TestB]
GO
 
USE [master]
GO
CREATE DATABASE [TestB] 
GO
 
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\Database\Backup\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
BACKUP DATABASE [TestB]
	TO DISK = @BackupPath
	WITH init;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
DBCC CHECKDB(TestB) WITH no_infomsgs;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
/* Now Restore the database */
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\DATABASE\BACKUP\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
/* for this contrived example, i will not take a tail log backup
and just use replace instead */
 
RESTORE DATABASE TestB
	FROM DISK = @BackupPath
	WITH REPLACE; 
GO
 
/* Rerun The Boot Page Check */
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO

If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ’1900-01-01 00:00:00.000′.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

Code block   
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)
 
CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum	INT
)
 
DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbccpage CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
		WHERE name not in ('tempdb');
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' +CHAR(10)+CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' +CHAR(10)+CHAR(13)
 
INSERT INTO #temp
	EXECUTE (@SQL);
SET @SQL = ''
 
INSERT INTO #DBCCRes
        ( DBName, dbccLastKnownGood,RowNum )
	SELECT @DBName, VALUE
			, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
		FROM #temp
		WHERE field = 'dbi_dbccLastKnownGood';
 
TRUNCATE TABLE #temp;
 
FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;
 
SELECT DBName,dbccLastKnownGood
	FROM #DBCCRes
	WHERE RowNum = 1;
 
DROP TABLE #temp
DROP TABLE #DBCCRes

You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.