Does SQL Server do a CHECKDB during an instance restart?

, 2014-05-13 (first published: )

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads