[Edit: From 2016 SP2 onward, you can use SELECT DATABASEPROPERTYEX (\u2018dbname\u2019 , \u2018LastGoodCheckDbTime\u2019 ); instead]<\/p>\n
This came up several times during the week so I thought it was about time to blog about it. One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB<\/em> completed successfully (called the last-known good time<\/em>). It’s the time that the DBCC CHECKDB *started*, not completed.<\/p>\n What does successfully<\/em> mean? This is the catch – if DBCC CHECKDB<\/em> runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is\u00a0ONLY updated if there were NO corruptions found. Slightly confusing I know.<\/p>\n Cool – but how can you see it? Well, the only time it’s ever reported is when the database starts up. Not too useful if the database has been running for months. So how to see it??? The trick is to use DBCC PAGE<\/em>. The\u00a0last-known good time\u00a0is stored in the boot page of the database – page 9. The following code will dump page 9 for you:<\/p>\n — you need this to get any DBCC PAGE output — page 9 is the boot page You need to look for the dbi_dbccLastKnownGood<\/em> field. That was the last time that DBCC CHECKDB<\/em> ran without finding any corruptions.<\/p>\n Now – what about if you’re trusting your SQL Agent jobs to run DBCC CHECKDB<\/em> for you every so often (how often is a whole other discussion…) and relying on the Agent job failing if DBCC CHECKDB<\/em> finds corruptions. An Agent job WILL fail if a DBCC CHECKDB<\/em> within it finds corruptions because DBCC CHECKDB<\/em> will set the @@ERROR<\/em>. Now here is a real issue – the Job History that’s captured will not contain all the output from DBCC CHECKDB<\/em> – especially if you didn’t use the WITH NO_INFOMSGS<\/em> option. You should make sure you capture the output to a file to avoid having to go back and run DBCC CHECKDB<\/em> all over again.<\/p>\n Summary – make sure you really know when DBCC CHECKDB<\/em> runs successfully, without<\/em> finding any corruptions!<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" [Edit: From 2016 SP2 onward, you can use SELECT DATABASEPROPERTYEX (\u2018dbname\u2019 , \u2018LastGoodCheckDbTime\u2019 ); instead] This came up several times during the week so I thought it was about time to blog about it. One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,30,31,34,85],"tags":[],"class_list":["post-1070","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-corruption","category-database-maintenance","category-dbcc","category-sql-agent"],"yoast_head":"\n
\nDBCC<\/span><\/span> TRACEON <\/span>(<\/span>3604<\/span>);<\/span><\/span>
\n<\/span>GO<\/span><\/p>\n
\nDBCC<\/span><\/span> PAGE <\/span>(<\/span>dbname<\/span>,<\/span> 1<\/span>,<\/span> 9<\/span>,<\/span> 3<\/span>);
\n<\/span><\/span>GO<\/span><\/p><\/blockquote>\n