If corruption is found in a database it’s very important to know when the corruption started. In the cases where a restore is needed to fix the corruption, knowing which backup is clean can save a great deal of time in restoring databases and checking them.
On SQL 2000, the only way to know was to to go back through the history of the checkDB jobs (everyone is running checkDB regularly, right?) and see when the last successful run was.
On SQL 2005 and higher, when checkDB runs successfully, ie without finding any errors, it writes the date of the last run into the database’s boot page. Whenever SQL Server then starts that database up, be it during a server start, a database restore or bringing the database online, SQL prints a message into the error log.
CHECKDB for database ‘Testing’ finished without errors on 2008-12-22 10:20:06.007 (local time).
This makes it very easy to see when the database was last known to be clean (without any corruption). Note that SQLL is not running checkDB when it prints that. It’s simply printing out the date that CheckDB last completed without finding any problems. The information can also be read out of the boot page using an undocumented DBCC command.
[source:sql]Use Testing
GO
DBCC TRACEON(3604)
DBCC DBINFO
DBCC TRACEOFF(3604)[/source]
This prints out a whole bunch of information, all found in the database’s boot page. The info of interest here is in the second block (on SQL 2008 RTM)
dbi_dbccLastKnownGood = 2008-12-22 10:20:06.007
If the date listed is 1900-01-01 00:00:00, it means that CheckDB has never run successfully on that database. If that is the case, there will be no entries in the error log when the database is started.
Gail, I’ve followed you on various boards (and Paul as well) and value your postings and thoughts on the whole gamut of topics. I run CHECKDB once a week (down time on Sunday’s). Never had an issue BUT was wondering what is your opinion on how often this should be run?? I have backups of all kinds (full, diffs and logs) that run regurlarly and with success, and tested. However, with all the “corruption” threads, I can’t help but think that possibly I should be running this even more frequently. Event viewer messages are checked with regularity and have been clean but I still can’t help being a little skittish. Your thoughts?
By the way – Happy New Year!
In my opinion, you should be running checkDB often enough that if it finds irreparable corruption, you can restore a clean backup and recover the database with acceptable data loss.
How often that is depends on your backup strategy, backup retention and acceptable data loss.
hi can you please tell me the trouble shooting issues on log shipping and repication
That has nothing whatsoever to do with this blog post. Please ask that on a SQL forum.
Pingback: First Major Event: Message 824Matt Velic | Matt Velic