I am running DBCC CHECKDB on one of my database but its taking very very long time. I have some questions please help me.

Is there any alternative of it that can check my database integrity?

Should I wait to finish it?

  • 1
    How Big DB are we talking here? – KASQLDBA May 14 '15 at 6:08
  • Also, what command have you run when you say DBCC CHECKDB – KASQLDBA May 14 '15 at 6:27

The only alternative to performing consistency checking (DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss.

There are some ways to reduce the impact that CHECKDB has on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full backup of your database onto another system and then running DBCC CHECKDB there. This is good for two reasons:

  1. It off loads the IO and perfomance issues your are experiencing onto another machine

  2. It tests your backups restore successfully.

If you can't afford to have a secondary server for testing backups and running DBCC CHECKDB then you may have to break the check down and run it against a few individual tables each night to spread the load out accross the week. You can do this with the CHECKTABLE statement. Another way to break the job into smaller parts would be to use CHECKFILEGROUP to check each file group, only makes sense if you have multiple file groups.

You can also try trace flags 2549, 2562, and 2566. For more information on the trace flags, the methods mentioned above and more read Aaron Bertrand's post on the subject here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.