HOW TO RECOVER THE SQL DATABASE FROM SUSPECT MODE

(Or)

HOW TO BRING BACK THE SUSPECTED DATABASE TO THE NORMAL MODE

When you login into the SQL management then you might have seen that one/some of your database has been Suspected State, because of the applications which run using this DB’s will not function and it will generate error while accessing those applications.

Cause:

Improper shutdown of the SQL server (in my case)

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

Ø  Not enough 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 below 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;

ALTER DATABASE [SharePoint_Config] SET MULTI_USER

Reference:

Source: https://msspadmin.blogspot.com/2014/07/how-to-recover-sql-database-from.html?showComment=1550127339127