sharepoint_config database in suspect mode

My SQL 2008 server’s data drives are on our SAN. Our SAN went down for a few minutes the other day. After our SAN came back up, my SharePoint configuration database was in “suspect” mode and I could not access SharePoint at all. “Cannot connect to the configuration database.”

I don’t know much about SQL, just enough to get SharePoint installed. So, I didn’t know how to fix this until I found this great post! http://blog.van-huizen.com/2009/10/how-to-repair-sql-server-2008-suspect.html

Unfortunately, it didn’t work for me. I tried the first command in a new query and it threw an error about not being able to connect to the database.

Eventually, I got all the commands to run, so I thought I’d add my notes to the post. Here’s what I did:

Open a command prompt. (Start | Run | cmd)

If you are using a default instance of SQL server open SQL command prompt by entering:

sqlcmd

If your SQL server is using a named instance other than the default enter this command instead (replacing servername and instancename with your server and instance names):

sqlcmd –Sservername/instancename

You should now have a 1> prompt. Enter the first command followed by a “GO” command. Replace YourDBname with the name of the Suspect database.

1>DBCC CHECKDB (’YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

2>GO

I had never used SQL command prompt before. didn’t know I needed to enter a “go” command after each command. Continue the same process for the rest of the commands.

1>EXEC sp_resetstatus ‘yourDBname’;

2>GO

1>ALTER DATABASE yourDBname SET EMERGENCY

2>GO

1>DBCC checkdb(’yourDBname’)

2>GO

1>ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2>GO

1>DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)

2>GO

1>ALTER DATABASE yourDBname SET MULTI_USER

After this, my database was back online and I could log into SharePoint.

REferences

• Tutorial: sqlcmd Utility
• DBCC CHECKDB (Transact-SQL)
• Creating, detaching, re-attaching, and fixing a suspect database (NOTE: This guide detaches and reattaches the database. I’ve seen/heard that it is a very bad idea to detach a suspect database.)
• How to repair a SQL Server 2008 Suspect database after upgrade to Windows server 2008 R2