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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-11 : 06:25:27
|
| Madhur writes "Last week DBCC CHECKDB <database name> took 2 Hrs to complete on a database.Today again i have started but with the following command:dbcc checkdb (<database name>, REPAIR_FAST ).It is now running for more than 2.5 Hrs now.Does the execution time increases even when the DB is consistent?Can we cancel the execution in the middle? What consequences it may have on the db?Thanks in advance." |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-11 : 09:16:10
|
| If you did not have any REPAIR commands before then it would have run more quickly.You could use the NOINDEX command - which would be faster, but would not prove that the indexes were in good shape.Do you have a "quite time" in which you could schedule the task, so that its duration doesn't bother any users much?Kristen |
 |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-11 : 15:07:13
|
quote:
Originally posted by AskSQLTeam
Madhur writes "Last week DBCC CHECKDB <database name> took 2 Hrs to complete on a database.Today again i have started but with the following command:dbcc checkdb (<database name>, REPAIR_FAST ).
Running any repair option makes CHECKDB use a different algorithm while checking the database. Instead of checking multiple tables/indexes in parallel to get the best possible IO throughput, it only checks a single table and its indexes at once so that it gets all the repairs ordered correctly. This will slow it down some. Don't get me started on why you shouldn't run just run repair all the time.quote:
Does the execution time increases even when the DB is consistent?
CHECKDB achieves a transactionally consistent view of the database by internally (i.e. inside itself, privately and with no effect on the actual database) recovering the transaction log. If there are many open transactions, it will have to spend time 'undo'ing them internally. The more concurrent update activity on the database, the slower CHECKDB may run.Also, the more concurrent select activity on the database, the slower it will run as it will be competing for the disk heads with other IOs.quote:
Can we cancel the execution in the middle? What consequences it may have on the db?
Yes, and nothing. If you're running repair, any repairs it has done will be rolled back.How big a database are we talking about here? And what kind of concurrent workload is running?Paul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
|
|
|
|
|
DBCC CHECKDB