Does SQL Server do a CHECKDB during an instance restart?

11

May 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.

11 thoughts on “Does SQL Server do a CHECKDB during an instance restart?

  1. Lee Everest says:

    Cool, thanks for sharing.

    I like your test db names too, btw 😀

  2. Sasan says:

    Thank you for sharing.

  3. aseniukAdam says:

    Great catch, I had always assumed it was a quick check.

  4. Thulasi says:

    Thanks for sharing the information. I do have this in my mind and which is clear now.

  5. manu says:

    Thanks for sharing the information. I always assumed it to be a quick check:)

  6. Anu says:

    thanks for sharing. good information.

  7. Wilfred says:

    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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: