Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cgunner
Yak Posting Veteran
95 Posts |
 Posted - 2007-01-19 : 13:58:42
|
| I have a 2.5TB database that we need to run checkdb on. Is there a formula to figure how log it would take with no options? Doing research, it appears the we are stuck with doing the PHYSICAL_ONLY due to time constraints. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-19 : 14:28:20
|
| No such formula exists.I'd restore your database to a different location then run DBCC CHECKDB there.Tara Kizer |
 |
|
|
cgunner
Yak Posting Veteran
95 Posts |
Posted - 2007-01-19 : 15:43:41
|
| We can do that but it may take a week to completely run. We will be in a time crunch again with our dev box being inaccessible. I think PHYSICAL_ONLY may be our only option. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-19 : 19:28:10
|
quote:
Originally posted by cgunner
We can do that but it may take a week to completely run. We will be in a time crunch again with our dev box being inaccessible. I think PHYSICAL_ONLY may be our only option.
physical_only is not a complete check though. It is a good check for physical problems like torn pages. You won't be performing any of the logical consistency checks if you use this option.I would be interested in what Paul Randal has to say, since he coded several of the DBCC routines we use - including DBCC CHECKDB.-ec |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-01-24 : 08:05:41
|
| Here's a link to a blog post from last year where I discuss consistency checking options for VLDBs such as yours:http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspxThere's no way to estimate how long a CHECKDB will take to run because it depends on many things including: - concurrent IO load on the database - for SQL 2000, concurrent update activity on the database - throughput capabilities of the IO subsystem - number of CPUS (parallel CHECKDB is a feature of Enterprise only) - complexity of the schema (i.e. what checks need to run) - what corruptions exist in the database that may trigger more time consuming checksMy advice to you is to do a run on your database with it in steady-state (note that I'm not saying it has to be quiescent) and see how long it takes.This is a good question for a blog post - I'll do one today where I go into more detail on the various factors.ThanksPaul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-24 : 08:23:07
|
Am I being over-simplistic in thinking that all DBs need CHECKDB running at regular intervals (to provide early warning of any corruption), and thus the approx. run-time will be known based on how long it normally takes?  Doesn't help for the first time CHECKDB is run of course ... but hopefully that is when the database is still in QAKristen |
|
|
|
cgunner
Yak Posting Veteran
95 Posts |
Posted - 2007-01-24 : 14:55:41
|
| Thanks all for the advice. We ran the check in QA this weekend on a 1.5TB DB. It ran for 7 hours prior to finding issues. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
|
|
|
|
|
DBCC Checkdb time estimate