Home / server / Questions / 409114
Accepted
marc_s
Asked: 2012-07-19 22:15:15 +0800 CST

SQL Server : CheckDB runs fast enough on one database, agonizingly slow on another

  • 772

This one's for you, Paul! :-)

I have a customer which has two databases for two of his office locations. Both are roughly the same size (around 10 GB), both are hosted at the same professional hoster's site - so give or take, they're the same.

Yet, database A completes its Check database integrity step in the maintenance plan in less than 30 minutes, while database B never even completes - the DBA killed the process after more than 3 hours of running.

These steps are being run "after hours" - so there shouldn't be any significant activity on the servers.

Any explanation? Any idea what to check for or where to look for a reason for this (and a way to solve it)? There are no error reported or anything.....

sql-server checkdb

2 Answers

  1. Best Answer

    DBCC CHECKDB doesn't run a DB check. It is actually a collection of commands that verify different aspects of a database. Relevant part from BOL:

    Runs DBCC CHECKALLOC on the database. Runs DBCC CHECKTABLE on every table and view in the database. Runs DBCC CHECKCATALOG on the database.

    If one DB takes 30 mins and another takes > 3 hours it is quite likely that it's the CHECKTABLE command. Esp. if the sizes of the databases is dramatically different.

    • 1

  2. Sorry, run check database integrity on the slow database and LET IT FINISH. It is likely it found issues and started going into details and - that took a lot more time than running over an ok database.

    Possibly execute the sequence to check the database from the command line ;) DBCC CHECKDB has some output that may help you ;) Maintenance plans are a little harder to debug.

    • 0