Recovering a SQL Server database from Suspect Mode

Yesterday my SharePoint environment didn’t respond I checked the DB server and got to know few SharePoint databases were in suspect mode. Now I want to make the databases up and running as usual in order to get my work done. This is a SQL Server thing. There could be several reasons for a database to go to the Suspect Mode. In my case my .ldf file was corrupted.

This forum thread explains more reasons why a database goes to Suspect Mode.

image

In order to get the database follow these steps. Login to the server using an account with sysadmin privileges. And set the target to database to EMERGENCY mode.

   1: ALTER DATABASE [db name] SET EMERGENCY

 

image

Then set the database to SINGLE USER mode in order to run the recovery.

   1: ALTER DATABASE [db name] SET SINGLE_USER

Now you can run the following command to rebuild the transactional log file. Here data loss is allowed.

   1: DBCC CheckDB ([db name], REPAIR_ALLOW_DATA_LOSS)

Then set back the database to MULTI USER mode.

   1: ALTER DATABASE [db name] SET MULTI_USER

 

Sometimes you might run into problems where you cannot set the database to MULTI USER mode; SQL Server complains with this error “The database is in single-user mode, and a user is currently connected to it.”