Adding when DBCC CHECKDB last ran to the SYS.DATABASES catalog view - by

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


24
0
Sign in
to vote
ID 350596 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 6/11/2008 8:15:29 PM
Access Restriction Public

Description

Currently it is not easy to determine when DBCC CHECKDB last ran successfully on a database. Of if you like when it last ran and the state value of that execution in the case of a failure. (The state values that are discussed in BOL at http://msdn.microsoft.com/en-us/library/ms176064.aspx)

Sure the information is written to the error log. 

You can also examine the dbi_dbccLastKnownGood value in page 9 of the database.

But it should be easier to get this critical information. 
Sign in to post a comment.
Posted by Aaron Nelson on 8/15/2016 at 6:41 PM
This is vital information is vital and should be included in SYS.DATABASES. The solution in the comments below is obviously useful, but only by the handful of customers who read this Connect item.

Given how vital this information is, closing this item with such a narrowly distributed solution is not acceptable. Since this information is so easily accessible (as evidenced by the solution contributed in the comments), it should not be hard to add it to SYS.DATABASES or at the very least to the database objects in SMO and exposed through Object Explorer Details (when you select the database level).
Posted by Microsoft on 12/1/2014 at 8:21 PM
Sankar: sorry for the late response. While the suggestion is useful, but there is a work-around like the one you had suggested, For this reason, we don't plan to fix it. I am closing this.

thanks
Sunil
Posted by Sankar Reddy on 12/25/2010 at 3:19 PM
Hi,

Is this fix included in DENALI?
Posted by Microsoft on 11/14/2009 at 6:54 PM
Shankar: glad to know that you found a solution for this. thanks
Posted by Microsoft on 11/14/2009 at 6:54 PM
Shankar: glad to know that you found a solution for this. thanks
Posted by Sankar Reddy on 6/15/2009 at 9:01 PM
I wrote an elegant solution to find this info.

CREATE TABLE #temp (
     Id INT IDENTITY(1,1),
     ParentObject VARCHAR(255),
     [Object] VARCHAR(255),
     Field VARCHAR(255),
     [Value] VARCHAR(255)
)

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(
    SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
)    
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp
Posted by Microsoft on 8/20/2008 at 11:05 AM
This issue has been approved/fixed and should be available in a future release of SQL Server. Thanks for providing us your feedback to SQL Server.
Posted by Microsoft on 6/17/2008 at 9:27 AM
thanks for you feedback. We will consider this in the next release.