Tuesday, May 8, 2012

Repairing a Failed WSS_Content Database

Let me start by stating that I am not a big SQL guy. I can find my way around when I have to, but I'm not in the expert zone. However, one of our clients just had a big issue with SharePoint 2010. Their server crashed and the WSS_Content database that holds the SharePoint site data was in Read Only mode. It was giving errors indicating that database files were not accessible. The log file was missing.

There are a lot of postings on the Internet about repairing databases in Emergency mode. However, only one had the process that worked for me in this case. Most articles just say to run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. However, my database was in Read Only mode and wouldn't allow it.

I was also unable to access any database properties through SQL Management Studio. So, I needed to perform most operations by using Transact-SQL when I was attempting to make repairs.

Here is the process that worked for me:
  1. Take the failed database offline and make a copy of it to use as a backup just in case.
  2. Stop all SharePoint 2010 services so that they are not attempting to log on to the databases.
  3. Create a new database named Recovery that uses the same file names in an alternate recovery location
  4. Take the new database offline
  5. Copy the old database file to the recovery location and replace the new empty database file.
  6. Start the Recovery database
  7. Put the Recovery database into Emergency mode: alter database recovery set emergency
  8. Put the Recovery database into single user mode: alter database recovery set single_user
  9. Repair the database with the option to reset/recreate the transaction log file: dbcc checkdb ('recovery',REPAIR_ALLOW_DATA_LOSS)
  10. Verify that the Recovery database mounted properly and I can view the tables.
  11. Drop the existing WSS_Content database: drop database wss_content
  12. Use SQL Management Studio to rename Recovery to WSS_Content.
  13. Start all SharePoint 2010 services.
  14. Verify that SharePoint 2010 is functional.
After doing some more research after the fact, it might have been possible to just kick it out of Read Only mode and do a repair. To take a database out of Read Only mode would have been done with: alter database WSS_Content set READ_WRITE

No comments:

Post a Comment