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 (called the last-known good time). What does successfully mean? This is the catch – if DBCC CHECKDB runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is ONLY updated if there were NO corruptions found. Slightly confusing I know.


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. The last-known good time is stored in the boot page of the database – page 9. The following code will dump page 9 for you:



– you need this to get the DBCC PAGE output to your console
DBCC TRACEON (3604);
GO


– page 9 is the boot page
DBCC PAGE (dbname, 1, 9, 3);
GO


You need to look for the dbi_dbccLastKnownGood field. That was the last time that DBCC CHECKDB ran without finding any corruptions.


Now – what about if you’re trusting your SQL Agent jobs to run DBCC CHECKDB for you every so often (how often is a whole other discussion…) and relying on the Agent job failing if DBCC CHECKDB finds corruptions. Well, if all you do is run the DBCC command, you’re never going to know about corruption unless DBCC CHECKDB itself fails for some reason – remember, it returns successfully even it it found corruptions.The key is to add another statement after you run DBCC that checks the value of @@ERROR (the last error code reported by SQL Server). Although DBCC CHECKDB doesn’t stop with an error when it finds corruption, it will set @@ERROR if there are any error messages in its output.


Edit: The stuff I’ve struck-through above is not true. I thought I’d remembered it from testing previously and I’ve heard plenty of anecdotal evidence from customers too BUT an Agent job WILL fail if a DBCC CHECKDB within it fails. I went back and forth with Tara Kizer on SQLteam.com and we tested on 2005 and 2000 – and it worked. Now here is a real issue – the Job History that’s captured will not contain all the output from DBCC CHECKDB – especially if you didn’t use the WITH NO_INFOMSGS option. You should make sure you capture the output to a file to avoid having to go back and run DBCC CHECKDB all over again.


Summary – make sure you really know when DBCC CHECKDB runs successfully, without finding any corruptions!

Related Posts