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
