How to Remove Database from Suspect Mode in SQL Server 2012?

Summary: This post discusses why a database goes into SUSPECT mode. Also, it explains the methods to remove database from suspect mode in SQL Server 2012.

You may find a SQL Server database marked as SUSPECT if the transaction log file is damaged, preventing database recovery or a transaction from rolling back. The transaction log can get corrupted due to any of these reasons:

• When the database was shut down abruptly and trying to access the database puts it in recovery. But, the recovery process fails due to corruption in data page or log record.

• The database was running some operations when the SQL Server crashed leading to corruption that prevents a transaction from rolling back.

Besides the above-mentioned reasons, a database can be in SUSPECT state due to insufficient disk space, faulty hardware, or corruption of data files.

Whatever be the reason behind SUSPECT database, you will need to bring it online

How to Remove SQL Server Database from Suspect Mode?

Method 1 – Restore from Backup

Restoring database from the last good backup is the best approach to recover the suspect database without data loss. But if the backup is not updated or is damaged, skip to the next method.

Method 2 – Use EMERGENCY Mode to Fix the SUSPECT Database

Once the database is set to EMERGENCY mode, bring it to SINGLE-USER mode for running the repair process. Next, repair the database by using the “DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS”. Let’s discuss the complete process step by step in detail:

Step 1: Set Database into EMERGENCY Mode

ALTER DATABASE 'DB1' SET EMERGENCY    

Note: If you can’t set the database to EMERGENCY state, make the database OFFLINE and then bring it ONLINE again.

Step 2: Set Database into SINGLE_USER Mode

The EMERGENCY mode allows multiple users who are members of the sysadmin role to access the database. However, to run the repair process, you will need to put the database in SINGLE_USER mode and use ‘WITH ROLLBACK IMMEDIATE’ to roll back any uncommitted transactions. To do so, execute this query:

ALTER DATABASE 'DB1' SET SINGLE_USER WITH ROLLBACK IMMEDIATE    

Step 3: Back up the Database

Now before running the repair process, back up your database with all the rolled back transactions. If anything goes wrong during db repair, you’ll have a backup you can restore to. 

Step 4: Repair the Database

Caution! Using the ‘REPAIR_ALLOW_DATA_LOSS’ involves risk of data loss. If the repair option find a corrupt record on a data page, it may delete that entire data page.

To repair the database, run this query:

DBCC CHECKDB ('DB1',REPAIR_ALLOW_DATA_LOSS)    

Note: If running the DBCC CHECKDB with the repair option fails, chances are that your database is severely corrupted. Skip to the next solution to repair corrupt SQL database.

Step 5: Set the Database in MULTI_USER Mode

For this, execute this command:

 ALTER DATABASE 'DB1' SET MULTI_USER    

Here’s the complete query you need to execute to remove database from SUSPECT mode and bring it to normal mode in SQL Server 2012:

ALTER DATABASE 'DB1' SET EMERGENCY   

ALTER DATABASE 'DB1' SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

DBCC CHECKDB ('DB1', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE 'DB1' SET MULTI_USER    

Step 6: Refresh the Server

Once you’ve completed all of the above steps, make sure to refresh the database server.

Method 3 – Use a SQL Repair Tool

If the repair method can’t fix the damaged database or you can’t risk losing any data, try using a third-party SQL repair tool.

Use Stellar Repair for MS SQL software to bring your suspect database to its normal form without any data loss. The software provides an enhanced preview of the recoverable database objects. You can get back all the data you can see in the preview.

Conclusion

A database goes into suspect mode because of corruption that prevents transactions from rolling back. Or, when database recovery won’t complete due to corrupt log records or data pages. You can change the database from SUSPECT mode to NORMAL mode by restoring it from the last known good backup or set the database in EMERGENCY state and run DBCC CHECKDB with repair option – but use it as a last resort as it can result in data loss. A better alternative is to use a third-party SQL repair tool to regain access to your DB.