T-SQL Tuesday #86 – Adding when DBCC CHECKDB last ran to the SYS.DATABASES catalog view

Brent Ozar is hosting TSQL2sDay #86 this month and in response to Brent Ozar’s Call for Important Connect Items I wanted to highlight this one about adding the date of the last known good DBCC CHECKDB to SYS.DATABASES. This one seems like a no-brainer to me. When you NEED this information, you don’t want to have to go hunting for it, and ever Microsoft agreed:
Posted by Sunil [MSFT] 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.

https://connect.microsoft.com/SQLServer/feedback/details/350596/

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

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.

The Good News:

The good news is that someone
Sankar Reddy figured out an easy way how to do it and posted their query in the comments on the Connect Item:

CREATE TABLE #temp
(
    Id INTIDENTITY(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

DROPTABLE #temp

The Bad News:

Unfortunately, since someone had come up with a work-around, an engineer at Microsoft thought that was good enough and decided not to give customers easy access to critical information.

“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”

Conclusion:

This item is not “Fixed”, at best it’s “Patched” thanks to a work-around someone offered up, but as Shane points out in the comments below, this work-around doesn’t even work for the largest customers.  It’s pretty obvious what needs to be done here, this item need to be re-opened and the column needs to be added to SYS.DATABASES.
Additionally, in the future, if you have a work-around for a Connect item that has been approved to be fixed, go ahead and blog about it & tweet about it, share it all over the place, but whatever you do, please don’t mention it on the Connect item itself!
(For the record: It pains me to have added that last sentence. I know the SQL engineers at Microsoft work hard to bring us great things, but the comments on this Connect item left me no other reasonable expectation of the potential outcome.)

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

2 Responses

    1. Shane,
      Thanks for the comment. You make a really great point and I didn’t realize that the solution wouldn’t work for everyone. I will update the post with that!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner