Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
    
What is the wait_type for the session that is running the DBCC command? You can retrieve this from sys.dm_exec_requests. Or from sys.dm_os_waiting_tasks, but it won't be so intuitive what session_id is running your DBCC command unless you look at the query window that shows that. –  Thomas Stringer Sep 19 '12 at 12:49
add comment

migrated from stackoverflow.com Sep 19 '12 at 14:53

This question came from our site for professional and enthusiast programmers.

1 Answer

can I terminate it without causing any problem on the DB

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.

I did take backup before I run it.

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.