Share via

SQL Server Troubleshooting: How to bring back the suspected database to the normal mode

  • Article

When you login using SQL management studio you might find one or more of your databases in Suspected State.  Any application using such a database will throw an error and fail to open the database.

Causes

Probable causes that bring Databases into Suspect Mode are:

  • Hardware failure
  • Improper shutdown of the SQL server
  • Corruption of the database files and log files
  • Insufficient disk space when writing data
  • Unavailable database files
  • Database resource used by operating system
  • SQL Server incorrectly asserts free data page space when a row is inserted

Resolution:

  To fix this issue follow these steps:

Change the status of your database

Right click on the database and select New Query and then Execute the following command.

EXEC sp_resetstatus DBNAME;

Ex: -   EXEC sp_resetstatus ‘SharePoint_Config’;

 

Set the database in “Emergency” mode

ALTER DATABASE DBNAME SET EMERGENCY

Ex: -  ALTER DATABASE  SharePoint_Config SET EMERGENCY

 

Check the database for any inconsistency

DBCC checkdb(‘DBNAME’)

Ex: -   DBCC checkdb(‘SharePoint_Config’)

 

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.
ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Ex: -   ALTER DATABASE SharePoint_Config SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 

Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database.

 

DBCC CHECKDB (' DBNAME ', REPAIR_ALLOW_DATA_LOSS);

Example: DBCC Checkdb (‘SharePoint_Config’, REPAIR_ALLOW_DATA_LOSS)

 

 Finally, bring the database in MULTI USER mode

 

ALTER DATABASE DBNAME SET MULTI_USER;

 

Example: ALTER DATABASE [SharePoint_Config] SET MULTI_USER