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 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01
00:00:00.000dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)dbi_dbccLastKnownGood = 1900–01-01 00:00:00.000 dbi_roleSequence
= 0dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2012-11-15
09:42:47.813dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c dbi_svcBrokerOptions =
0x00000001
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 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01
00:00:00.000dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)dbi_dbccLastKnownGood = 1900–01-01 00:00:00.000 dbi_roleSequence
= 0dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2012-11-15
09:42:47.813dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c dbi_svcBrokerOptions =
0x0000000
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 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01
00:00:00.000dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)dbi_dbccLastKnownGood = 2012-11-15
12:25:34.090 dbi_roleSequence
= 0dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2012-11-15
09:42:47.813dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c dbi_svcBrokerOptions =
0x0000000
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 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01
00:00:00.000dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)dbi_dbccLastKnownGood = 2012-11-15
12:45:21.173 dbi_roleSequence
= 0dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2012-11-15
09:42:47.813dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c dbi_svcBrokerOptions =
0x0000000
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.
12 thoughts on “What Checks Update dbccLastKnownGood?”
Hi Erin,
Am curious to know what exactly “dbi_modDate” and “dbi_crdate” return. To me, They sound like last DB modified date and DB Creation date respectively, but am seeing inconsistent results in my test environments. Any thoughts…?
Hi Sreekanth-
dbi_modDate is the date the database was initially created, and dbi_crdate is the date it was created on the instance. If you create a database on an instance, they’ll be the same. If you restore it to another instance, you’ll see different values.
Erin
Erin,
Thanks for the clarification between dbi_crdate and dbi_modDate. I have a database that shows dbi_modDate = 1900-01-01 00:00:00.000. It also has a dbi_createVersion = 539 (which I understand means it was originally created in SQL 2000). In SQL 2000, the dbi_modDate must not have been tracked, and then when this database was upgraded, the dbi_modDate simply was added as 1900-01-01 00:00:00.000.
Sean-
How was the database upgraded? I’m guessing that there was a new instance on a new server? And then was the database restored to that instance, or was it detached from the original then attached to the new instance? Also, you went from SQL Server 2000 to what version?
Thanks!
Erin
It is currently a 2008R2 database. It is a vendor database so I’m not sure how it was upgraded. But, yes, it was restored to a new instance on a new server (our instance/server vs. the vendor original server).
Wow…I always used to think dbi_modDate gives “Modified date” 🙁
Now it all makes sense to me, looking at my results. Thanks Erin for Documenting the Undocumented DBINFO() command for me 🙂 Have a Great day…!
if you offload checkdb to run in the Availability Group secondary db, then it wont update the dbi_dbccLastKnownGood, only running it on the primary will.
Bill-
Correct, a check on a secondary copy will not update the boot page in the primary copy. Neither will a check against a snapshot. And, a check on the secondary also doesn’t ensure there is no corruption in the primary, as pages are not copied or replicated from primary to secondary, transactions are replayed. The only way to know if there corruption on the primary is to run it there, or take a backup of the primary and restore it and run it (and then, even if you find corruption there, you still need to run the check on the primary, as the backup storage could be the problem).
Thanks,
Erin