How to fix a suspect mode database?

Scenario

A database in Suspect mode is one that is not fully operational and may have some form of corruption or problem. If you are experiencing this issue, you will need to try to identify the cause of the problem and take steps to fix it.

There are a few ways to fix a suspect mode database in SQL Server. The best way depends on the specific cause of the corruption.

There are a few different reasons why a database might end up in SUSPECT mode. Some of the most common include:

Hardware or software failures
Data or log file corruption
Power outages or other disruptions
Incorrectly terminated database sessions

To try to fix a database that is in SUSPECT mode, you can try the following steps:

Check the error log for any messages that might indicate the cause of the problem.
If the database is in SUSPECT mode because of a hardware or software failure, try to repair or replace the faulty component.
If the database is in SUSPECT mode because of data or log file corruption, you can try to repair the database using the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option. This will try to repair the database, but it may result in some data loss.
If the database is in SUSPECT mode because of a power outage or other disruption, you may be able to bring it back online by performing a recovery.

How to Recover MS SQL Database from Suspect Mode

Reason for suspect mode:
1. If one or more database files are not available.
2. If the entire database is not available.
3. If one or more database files are corrupted.
4. If a database resource is being held by the operating system.

How to recover?

Scenario 1: If the file is full

Execute sp_resetstatus
Syntax: sp_resetstatus database_name
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

Scenario 2: If the data file was damaged

Take T.Log backup
Restore last Full backup
Restore T.Log backup
Database comes online

Scenario 3: If the T.Log file was damaged

Take any user defined db for example: MyDB
Check the current location of files
sp_helpdb MyDB
Stop server
Move the T.Log file into different folder
Start server –> DB goes into suspect mode
Select databasepropertyex (‘mydb’,’status’)

Steps to Recover

Step1: Make the db into single user
1) Alter database mydb set Single_User
Step2: Set the db into emergency mode
2) Alter database mydb set Emergency
Step3: Run checkdb with required repair level
3) DBCC CheckDB (‘mydb’, REPAIR_ALLOW_DATA_LOSS)
Step4: Set the db into multi user mode
4) Alter database mydb set Multi_UserISSUE

If these steps do not help, you may need to restore the database from a backup. It is important to always have recent backups of your databases in case of issues like this.

Tags: Suspect Mode