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.)
The post T-SQL Tuesday #86 - Adding when DBCC CHECKDB last ran to the SYS.DATABASES catalog view first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
