SQL server database in SUSPECT MODE

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 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:

1. Check the error log for any messages that might indicate the cause of the problem.
2. If the database is in SUSPECT mode because of a hardware or software failure, try to repair or replace the faulty component.
3. 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.
4. 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.

SQL server database in SUSPECT MODE Solution

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.

Scenario2: If the data file was damaged

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

Scenario3: 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.