Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Does SQL Server do a CHECKDB during an instance restart?

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...