When you start up an instance do you rely on the CHECKDB entry in the error log of SQL Server to tell if your databases are corrupt? You shouldn’t be.
Ever wonder why the CHECKDB can run so fast when you start up your instance but takes so long when you do it manually? Simple answer, it isn’t running at all. Here are the “CHECKDB” entries from my SQL 2012 instance that I just re-started.
2014-04-22 12:55:52.09 spid9s CHECKDB for database 'model' finished without errors on 2014-04-19 10:08:18.273 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:52.92 spid16s CHECKDB for database 'msdb' finished without errors on 2014-04-19 10:08:18.873 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.40 spid21s CHECKDB for database 'DatabaseB' finished without errors on 2014-04-19 10:08:21.737 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.40 spid24s CHECKDB for database 'TruncateTest' finished without errors on 2014-04-19 10:08:23.237 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.41 spid23s CHECKDB for database 'ImpTest' finished without errors on 2014-04-19 10:08:22.813 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.41 spid19s CHECKDB for database 'DatabaseA' finished without errors on 2014-04-19 10:08:21.207 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.41 spid18s CHECKDB for database 'PartialyContained' finished without errors on 2014-04-19 10:08:20.957 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.41 spid17s CHECKDB for database 'Tests' finished without errors on 2014-04-19 10:08:20.350 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.41 spid22s CHECKDB for database 'DatabaseC' finished without errors on 2014-04-19 10:08:22.197 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:53.45 spid16s CHECKDB for database 'CollationTest' finished without errors on 2014-04-19 10:08:23.743 (local time). This is an informational message only; no user action is required. 2014-04-22 12:55:54.27 spid24s CHECKDB for database 'NewDBTest' finished without errors on 2014-04-19 10:08:24.500 (local time). This is an informational message only; no user action is required.
Note the restart date is 2014-04-22 12:55:52 but if you scan to the right you will see that the message says that CHECKDB finished without errors on 2014-04-19 10:08:18 several days earlier. As it happens I know that this is the last time I ran CHECKDB on these databases. The log is just displaying the results of the last time it was run.
So what does this mean for us? Primarily it means that after an instance has crashed (say someone tripped over the UPS cord) (no I’m not kidding) you should actually run DBCC CHECKDB on all of your databases rather than just relying on what’s in the log. This adds to your checkout time but potentially saves you a world of hurt if one of your DBs actually did turn up some corruption issues.
Filed under: DBCC, Log File, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: DBCC, microsoft sql server, system functions 
