I had to run dbcc checkdb repair_allow_data_loss with all_errormsgs and it's been runing over 27 hours. DB size is over 15gb and the server (windows 2008) has 32gb memory. I did run this on SQL 2005 and as I said it's been going over 27 hours. Is this normal? If not, can I terminate it without causing any problem on the DB? I did take backup before I run it.
migrated from stackoverflow.com Sep 19 '12 at 14:53
You already caused the problem, simply by running repair_allow_data_loss. Allow data loss is really the very very very last resort, after all other options have been exhausted. The appropriate action is to restore from a correct backup and re-apply missed transactions.
Can DBCC take 27 hours? Yes, I know of DBCC commands that took well over 8 days to complete. You can investigate and see if DBCC is blocked (and Shark give you the right approach for that) and if you're lucky it is blocked and you can unblock it and let it finish. But if is making progress though there is not much you can do but wait it out
If you terminate it, will the database be any worse that it already is? In theory no, because DBCC, like any other operation, is transitionally consistent and any in-flight operation will be rolled back. However, in practice, the very fact that you're running DBCC repair means you're dealing with corrupted structures, and in such case there is always a risk.
Validate the backup by restoring it on a different machine or as a different DB on the same instance. If the backup is correct, then you're in a much better place because you can always return to this backup. Also, make sure no new business transactions occurred after you took the backup.