One of the methods I’ve used to check when a client last ran DBCC CHECKDB on their database is the dbccLastKnownGood value on the boot page (page 9 in the PRIMARY file).  When working with a client a couple weeks ago, the value for dbccclastknowngood was from 2006, but the customer stated they were running integrity checks.  When I investigated their method for checks, I discovered that due to the database size they were running DBCC CHECKALLOC and DBCC CHECKCATALOG weekly, and checking different user tables with DBCC CHECKTABLE at night.  Breaking out the checks between the different commands does not update dbccLastKnownGood – running CHECKDB is the only thing that does.  Let’s have a look…

I have a copy of the AdventureWorks2012 database installed, and I can look at the boot page using DBCC PAGE or DBCC DBINFO.  Both commands are undocumented, but as Paul notes in the links provided, they are safe to run.  I’ll use DBINFO:

DBCC TRACEON (3604);
GO
DBCC DBINFO ('AdventureWorks2012');
GO

The text below is a snippet of the output to show only what’s relevant:

dbi_dbmOldestXactLsn = 0:0:0 (0×00000000:00000000:0000)                  dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0×00000000:00000000:0000)             

dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0×00000000:00000000:0000)                    dbi_localState = 0

dbi_safety = 0                      dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376              dbi_lazyCommitOption = 0           

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                 dbi_svcBrokerOptions =
0×00000001

If we run DBCC CHECKALLOC, DBCC CHECKCATALOG, and DBCC CHECKTABLE for all system and user tables, you can see that dbccLastKnownGood good does not change (please don’t holler because I’m using a cursor…it does what I need for this example!):

DBCC CHECKALLOC ('AdventureWorks2012') WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

DBCC CHECKCATALOG ('AdventureWorks2012');
GO

USE [AdventureWorks2012];
GO

DECLARE @schemaname varchar(500);
DECLARE @tablename varchar(500);
DECLARE @fulltable varchar(1000);
DECLARE @string varchar(5000); </span>

DECLARE TableList CURSOR FOR
SELECT [ss].[name],[so].[name]
FROM [sys].[objects] AS so
JOIN [sys].[schemas] AS ss ON [so].[schema_id]=[ss].[schema_id]
WHERE [so].[type] in ('S','U')
ORDER BY [so].[name]; 

BEGIN
OPEN TableList

FETCH NEXT FROM TableList
INTO @schemaname, @tablename;

WHILE @@FETCH_STATUS = 0>
BEGIN
SET @fulltable = @schemaname + '.' + @tablename;
SET @string = N'DBCC CHECKTABLE (''' + @fulltable + ''') WITH ALL_ERRORMSGS, NO_INFOMSGS;'
EXEC (@string)
FETCH NEXT FROM TableList
INTO @schemaname, @tablename;
END 

CLOSE TableList;
DEALLOCATE TableList;
END

<sDBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0×00000000:00000000:0000)                  dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0×00000000:00000000:0000)             

dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0×00000000:00000000:0000)                    dbi_localState = 0

dbi_safety = 0                      dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376              dbi_lazyCommitOption = 0           

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                 dbi_svcBrokerOptions =
0×0000000

No change in dbccLastKnownGood.  What happens if we run DBCC CHECKFILEGROUP?  I created a second filegroup in the AdventureWorks2012 database and created one table in the filegroup, then ran the check on that filegroup:

ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [TestFG]; 

ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TestFile', FILENAME = N'D:\Databases\SQL2012\AdventureWorks2012\TestFile.ndf' ,
SIZE = 4096KB , FILEGROWTH = 1024KB )
TO FILEGROUP [TestFG];

CREATE TABLE [dbo].[test] (
[col1] INT,
[col2] INT
)
ON [TestFG]; 

INSERT INTO [dbo].[test] (
col1,
col2
)
VALUES
(1,2),
(3,4); 

DBCC CHECKFILEGROUP (2) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
DBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0×00000000:00000000:0000)                  dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0×00000000:00000000:0000)             

dbi_dbccLastKnownGood = 2012-11-15
12:25:34.090                         
dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0×00000000:00000000:0000)                    dbi_localState = 0

dbi_safety = 0                      dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376              dbi_lazyCommitOption = 0           

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                 dbi_svcBrokerOptions =
0×0000000

Look at that, it updated.  And finally, we know that CHECKDB will update the value, but what happens if we include the WITH PHYSICAL_ONLY option, where the checks are not quite as involved? (See Paul’s post on check options for VLDBs for additional notes on WITH PHYSICAL_ONLY.)

DBCC CHECKDB ('AdventureWorks2012') WITH PHYSICAL_ONLY;
GO
DBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0×00000000:00000000:0000)                  dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0×00000000:00000000:0000)             

dbi_dbccLastKnownGood = 2012-11-15
12:45:21.173                         
dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0×00000000:00000000:0000)                    dbi_localState = 0

dbi_safety = 0                      dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376              dbi_lazyCommitOption = 0           

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                 dbi_svcBrokerOptions =
0×0000000

The dbccLastKnownGood value does update, as it does with CHECKDB without the PHYSICAL_ONLY.  Therefore, if you’re relying on dbccLastKnownGood to tell you when CHECKDB was last run, realize it may not give you the entire story.

Note: post edited 11/16/2012 to replace screen shots of DBCC DBINFO output with text, as the images did not render well in all browsers.