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:

DBCC TRACEON (3604);
GO
DBCC PAGE ('[Database name]', 1, 9, 3);
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.


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:

DBCC TRACEON (3604);
GO
DBCC PAGE ('dba', 1, 9, 3);
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:


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:9)


BUFFER:


BUF @0x0000000409FF4400

bpage = 0x0000000409D10000           bhash = 0x0000000400FD7C00           bpageno = (1:9)
bdbid = 15                           breferences = 0                      bUse1 = 11248
bstat = 0xec00309                    blog = 0x89898989                    bnext = 0x0000000000000000

PAGE HEADER:


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             


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Comments

Popular Posts