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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
It off loads the IO and perfomance issues your are experiencing onto another machine
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