Whenever there is an issue of data corruption on a database, everyone would be curious to know when was that database reported to be clean? This is a genuine question which is aimed at restoring the database from the backup which was taken when the database did not have any issues. Even I was asked the same question when the disk containing one of our biggest databases started reporting errors.
How to tell this? Of course the database is supposed to be clean when DBCC CHECKDB completes successfully without reporting any errors. Supposing that there is a scheduled job which runs DBCC CHECKDB the output file of that job would tell the story. Unless the output file for every run of that job is saved, it is practically impossible to tell when the CHECKDB reported no errors.
This was the issue until SQL Server 2000. Starting from SQL Server 2005, many of the DBAs tasks were made simpler. One of them is checking the last successful run of CHECKDB on a database. Where to find that information? Any configuration changes needs to be done? Any undocumented trace flags to be enabled?
Not at all. At times the toughest questions have unbelievably easy answers. It is in the SQL Server Error Log! When SQL Server starts, it tries to recover all the databases. When the recovery of a database is complete, it checks if CHECKDB was ever run on that database. If it was run, it will mention when the CHECKDB was run successfully against that database. If it was not it does not write anything to the error log.
Here is an example. I ran CHECKDB against a database named Pradeep and restarted the SQL Server service. Here is what I see in the error log.
Cool! This feature for sure save a lot of time for the DBA working on a corrupt database. I am not lucky this time because the database I mentioned earlier runs on SQL Server 2000 🙁