Determining How Long It Takes for DBCC CHECKDB to Run

Q: If I ever have to perform disaster recovery, I’m sure that running DBCC CHECKDB will be part of my disaster recovery plan. How can I estimate how long it will take for DBCC CHECKDB to run?

A: There’s a very simple answer to your question—run it and see how long it takes. However, there’s obviously a lot more to it than that, so let me explain.

Given that you’re going to be running DBCC CHECKDB, it’s a good idea to know how long it’s going to take to run for two reasons. First, if it takes longer than usual, that’s a sign that it has found corruption and is determining exactly where the corruption is. Second, if your boss comes along during the disaster recovery process and asks how long it will be before you know the extent of the damage, it’s far better to be able to say, “Well, it usually takes X hours to run” rather than “I have no idea, boss—I never run DBCC CHECKDB.”

As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. (I even have a hard time estimating for large databases, and I wrote DBCC CHECKDB!) The following are some factors that affect DBCC CHECKDB’s run time:

  • The size of the database. This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it. A 1TB database with only 100MB of data can be consistency checked very quickly, but if the same database contained 1TB of data, it would take a lot longer for DBCC CHECKDB to process.
  • The load on the system. DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
  • The capabilities of the system. If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
  • The options specified. If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time. However, I wouldn’t recommend using this option during disaster recovery.
  • The complexity of the database schema. The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
  • The corruptions that are found. Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
  • The tempdb configuration. DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down.

As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it.

Discuss this Blog Entry 2

Karam (not verified)
on Oct 17, 2010
Can you believe , it takes 1 day and 15 hours and it was still running (DB size : 23 GB, RAM : 4 GB, Dual core processor), then My my Boss decide to stop it and find another solution.
then we stopped it , we did the following steps:
- We detach the DB from SAN Storage
- Copying DB from SAN to C Drive
- Reatach DB from C , it is up and running
- During the week end we moved it back to SAN.

(By the way, it was marked as suspect befor DBCC)










Neotropic (not verified)
on Aug 30, 2010
Now... if only one of these hundreds of websites would tell you how to 'actually' execute this!
Not one site says 'Now type this command into...'.
Nope, just says: 'Just Run This!'

Execute through SSH, MySQL what?!?! If going through MySQL and fails, then do....

Too many vague instructions from 'professional' programmers :)





Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×