When did CheckDB last run?

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.

1. Use Testing
2. GO
3.
4. DBCC TRACEON(3604)
5. DBCC DBINFO
6. DBCC TRACEOFF(3604)

Use Testing GO DBCC TRACEON(3604) DBCC DBINFO DBCC TRACEOFF(3604)

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.