DBCC CHECKDB taking a very long time to run on 500MB database

Question

DBCC CHECKDB taking a very long time to run on 500MB database

mjjordan

Old Hand

Points: 310
More actions
January 21, 2013 at 7:38 am

#269294

I want to start by advising that I am new to MS SQL so I ask for your patience.
We lost access to our Sharepoint 'companyweb'.
I tried deleting the site and recreating it in SharePoint but this did not work as we are still without companyweb access. During the process I received errors stating that ShareWebDb was not accessible.
After Googling around I determined that the ShareWebDb database was at the root of my issues.
I ran the following on the ShareWebDb
-- CHECK THE STATUS OF THE DATABASE
SELECT DATABASEPROPERTYEX('ShareWebDb', 'STATUS') as 'DBStatus'
This lead to me finding the database was in 'SUSPECT' status
I then ran the following to set the database into 'emergency status'
-- IF DATABASE IS 'SUSPECT' set to 'emergency'
ALTER DATABASE ShareWebDb SET EMERGENCY
I then set the database into Single user mode;
-- Set database to single user mode to run checkdb utility
ALTER DATABASE ShareWebDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then started the DBCC CHECKDB to start he repair process.
-- run the checkdb command
DBCC CHECKDB (ShareWebDb, REPAIR_ALLOW_DATA_LOSS)
The database is only about 500MB and the above has been running for 4 days 17 hours....
I am thinking there's an issue with this process but if it takes this long i don't want to lose the 4 days of processing I have already run. I know that DBCC CHECKDB is a single thread process and can therefore result in it taking some time to run but I am thinking that to process 500MB of data shouldn't take this long.
I checked the status of the process by running
select * from sys.dm_exec_requests percent_complete where session_id = 56
and it shows the status as 'suspended'.
My question is, is it reasonable to think this process is going to take 4+ days to run on a 500MB database or should I kill it and try another approach? How can see/determine how far along the DBCC CHECKDB process is?
If another approach is advisable what is your recommended approach to resolving this issue?
Thanks for your time and knowledge in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Firstly, why did you decide to go for an emergency mode repair (that may very well result in data loss) instead of restoring a backup?

Emergency mode repair is the very last resort for when all else has failed and there are no backups. It's not guaranteed to always work. On a sharepoint database it's especially risky because the site structure is stored in the database and hence repairing and discarding data may leave the site in a non-functional state.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mjjordan Old Hand Points: 310 More actions · Answer 2

Thanks for the response.

Based on what I had found with my searches I was under the impression that using a backup was a last resort after attempting a repair.

It is apparent that this was not the best course of action.

I take you are recommending going killing the process that has been running and go to the backup?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

mjjordan (1/21/2013)
Based on what I had found with my searches I was under the impression that using a backup was a last resort after attempting a repair.

No. The backup is usually the best approach. Repairing with the allow_data_loss option loses data. What data, I can't say without a lot more information, but checkDB's not particularly polite. It will discard anything that's damaged and patch up the rest. You could get all sorts of transactional inconsistencies and orphaned data from running CheckDB with repair allow data loss. Since sharepoint keeps the site structure in the database, you could end up with an unusable site.

I take you are recommending going killing the process that has been running and go to the backup?

If you have a good backup that's recent enough that restoring it is acceptable, absolutely yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mjjordan Old Hand Points: 310 More actions · Answer 4

Thank you once again for your response.

Fortunately for me we do not make a lot of change to the Sharepoint sites and thus far only companyweb has become inaccessible, the other sites are accessible. I should therefore be able to restore the backup and move on.

Thanks again, appreciate the advice.