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
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;
GOIf 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.
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,
RowNumINT
)
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 #DBCCResYou 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.
