Identifying when last time dbcc checkdb executed successfully on a database…
This can be a great tool for a professional to troubleshoot a situation with database corruption or even for auditing purpose. Imagine a situation when you need to know when a database was not corrupted and hence you need to figure out when a last successful dbcc checkdb was executed on the database.
Easiest option is to refer SQL Server log for such information but this may not be a viable solution always. From SQL Server 2005 onwards, last time when DBCC CHECKDB is executed successfully is logged in boot page of the corresponding database. Please note that successful execution of DBCC CHECKDB is actually the time when DBCC CHECKDB was executed last time and reported “NO CORRUPTION”.
This information is already shown in SQL Server Log whenever the database starts but you may have already lost the log. Also there is a chance that DBCC CHECKDB successfully executed at a later date.
Hence follow below procedure to figure out when last time dbcc checkdb executed successfully on a database.
Step 1: As this information is stored in Page 9 (Boot page) of the database, use below code to dump contents of Page 9:
GO
GO
Step 2: Look for dbi_dbccLastKnownGood in the dump output. You will get the exact date/time when last time DBCC CHECKDB was successfully executed on the database.
Note:
However this procedure will not work in SQL 2000 or in earlier versions. In fact, there is no way to retrieve this information with out referring SQL Server Log in any version of SQL Server prior to SQL Server 2005.
For more details, please refer to http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-When-did-DBCC-CHECKDB-last-run-successfully.aspx.
Example execution of the procedure:
Here I will find when DBCC CHECKDB was executed successfully on a database named “dba” using the Steps mentioned above:
Step 1: I connected with the SQL Instance (2005 EE SP3 64 Bits on Win 2008 EE) and executed below code:
GO
GO
Step 2: Output from above command is analyzied and I found last successful CHECKDB execution was on “2010-04-24 01:01:14.967”. Please refer to Yellow highlighted portion of the below dump for details:
BUFFER:
BUF @0x0000000409FF4400
bpage = 0x0000000409D10000 bhash = 0x0000000400FD7C00 bpageno = (1:9)
bdbid = 15 breferences = 0 bUse1 = 11248
bstat = 0xec00309 blog = 0x89898989 bnext = 0x0000000000000000
Page @0x0000000409D10000
m_pageId = (1:9) m_headerVersion = 1 m_type = 13
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7362 m_freeData = 828
m_reservedCnt = 0 m_lsn = (169943:360:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 396849722
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0, Offset 0x60, Length 732, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =
Memory Dump @0x000000001D97C060
0000000000000000: 0000dc02 63026302 00000000 00000000 †....c.c.........
0000000000000010: 00000000 00000000 00000000 00000000 †................
0000000000000020: 00000000 08000100 9357ea08 91b7f000 †.........W......
0000000000000030: 519d0000 64006200 61002020 20202020 †Q...d.b.a.
0000000000000040: 20202020 20202020 20202020 20202020 †
0000000000000050: 20202020 20202020 20202020 20202020 †
0000000000000060: 20202020 20202020 20202020 20202020 †
0000000000000070: 20202020 20202020 20202020 20202020 †
0000000000000080: 20202020 20202020 20202020 20202020 †
0000000000000090: 20202020 20202020 20202020 20202020 †
00000000000000A0: 20202020 20202020 20202020 20202020 †
00000000000000B0: 20202020 20202020 20202020 20202020 †
00000000000000C0: 20202020 20202020 20202020 20202020 †
00000000000000D0: 20202020 20202020 20202020 20202020 †
00000000000000E0: 20202020 20202020 20202020 20202020 †
00000000000000F0: 20202020 20202020 20202020 20202020 †
0000000000000100: 20202020 20202020 20202020 20202020 †
0000000000000110: 20202020 20202020 20202020 20202020 †
0000000000000120: 20202020 20202020 20202020 20202020 †
0000000000000130: 20202020 06000000 0f005a00 505ac701 † ......Z.PZ..
0000000000000140: 00000000 00000000 00000000 00000000 †................
0000000000000150: 00000000 00000000 00000000 479b0200 †............G...
0000000000000160: f8200000 28000200 00000000 00000000 †. ..(...........
0000000000000170: 00000000 00000000 00000000 00000000 †................
0000000000000180: 3940ee01 00000000 08d00034 00000000 †9@.........4....
0000000000000190: 00000061 00000000 72e61057 a4704c40 †...a....r..W.pL@
00000000000001A0: baa43f1e bc0b6af4 0090700e 00000000 †..?...j...p.....
00000000000001B0: 00000000 00000000 00000000 00000000 †................
00000000000001C0: 00000000 00000000 72e61057 a4704c40 †........r..W.pL@
00000000000001D0: baa43f1e bc0b6af4 00000000 00000000 †..?...j.........
00000000000001E0: 00000000 00000000 00000000 00000000 †................
00000000000001F0: 00000000 00000000 00000000 00000000 †................
0000000000000200: 00000000 14000000 01006302 00000000 †..........c.....
0000000000000210: 00000000 00000000 00000000 00000000 †................
0000000000000220: 00000000 00000000 00000000 00000000 †................
0000000000000230: 00000000 00000000 00000000 00000000 †................
0000000000000240: 00000000 00000000 00000000 00000000 †................
0000000000000250: 00000000 00000000 00000000 00000000 †................
0000000000000260: 00000000 00000000 00000000 00000000 †................
0000000000000270: 00000000 00000000 00000000 00000000 †................
0000000000000280: 11000000 68000000 25000000 9ad21000 †....h...%.......
0000000000000290: 629d0000 00000000 00000000 00000000 †b...............
00000000000002A0: 00000000 91b7f000 519d0000 c30f0009 †........Q.......
00000000000002B0: 00000000 050d45f6 21eaf749 9eb806af †......E.!..I....
00000000000002C0: c6561f19 01000000 00000000 00000000 †.V..............
00000000000002D0: 00000000 00000000 00000000 ††††††††††............
DBINFO @0x000000001D97C060
dbi_dbid = 15 dbi_status = 65544 dbi_nextid = 149575571
dbi_dbname = dba dbi_maxDbTimestamp = 29842000 dbi_version = 611
dbi_createVersion = 611 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2010-04-07 14:36:25.443
dbi_filegeneration = 0
dbi_checkptLSN
m_fSeqNo = 170823 m_blockOffset = 8440 m_slotId = 40
dbi_RebuildLogs = 0 dbi_dbccFlags = 2
dbi_dbccLastKnownGood = 2010-04-24 01:01:14.967
dbi_dbbackupLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_oldestBackupXactLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_differentialBaseLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_createIndexLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_versionChangeLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_familyGUID = 5710e672-70a4-404c-baa4-3f1ebc0b6af4
dbi_recoveryForkNameStack
entry 0
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
m_guid = 5710e672-70a4-404c-baa4-3f1ebc0b6af4
entry 1
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 00000000-0000-0000-0000-000000000000 dbi_firstSysIndexes = 0001:00000014
dbi_collation = 872468488 dbi_category = 0 dbi_maxLogSpaceUsed = 242257920
dbi_localState = 0 dbi_roleSequence = 0
dbi_failoverLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbmRedoLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbmOldestXactLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_disabledSequence = 0
dbi_dvSplitPoint
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_CloneCpuCount = 0 dbi_CloneMemorySize = 0
Comments