Does SQL Server do a CHECKDB during an instance restart?
11May 7, 2014 by Kenneth Fisher
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.
SQL Studies 
Cool, thanks for sharing.
I like your test db names too, btw 😀
Thanks! What can I say. They are descriptive names for the test I was running at the time 🙂
Thank you for sharing.
My pleasure.
Great catch, I had always assumed it was a quick check.
We’d been doing the same thing in our office. Never caused us a real problem but it certainly could have.
Thanks for sharing the information. I do have this in my mind and which is clear now.
Thanks for sharing the information. I always assumed it to be a quick check:)
thanks for sharing. good information.
Too bad SQL isn’t doing an automated DBCC after it has detected a crash. This brings me to this question. Is there any option to check if the last shutdown was not normal?
Checking the log would be your best bet. and/or checking out the default trace.