Skip to main content

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.

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:

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release
Post a Comment
Read more

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [
Post a Comment
Read more

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch 
Post a Comment
Read more