The ‘Suspect’ mode of the SQL database indicates that the recovery process has started but failed to complete. The database goes into suspect mode if the primary data file is damaged or the database file is missing. In the suspect mode, the database is unavailable to users. The database will stay in the suspect mode until it is made available. In this article, we'll discuss why SQL database goes into suspect mode and how to recover MS SQL database from suspect mode.
Why a SQL Server Database Goes into “Suspect Mode”?
The SQL database state changes to suspect mode due to various reasons. Some of them are:
- Corruption in the database file
- Missing database files
- The database's primary file group is damaged or corrupted
- The database is terminated abnormally
- System disk is out-of-space
- Missing transaction log files
- Log files are corrupted
- SQL cannot complete a rollback or roll-forward operation
- SQL server shuts down or crashes in the middle of a transaction
Methods to Recover SQL Database from Suspect Mode
Below are some methods you can follow to get the database out of the suspect mode.
Method 1: Restore Database from Backup
If you have a healthy and updated database backup, you can try to restore the database from the backup. To restore the database from backup, follow these steps:
- Launch SQL Server Management Studio (SSMS) and connect to the database engine.
- From the standard bar, click New Query.
- In the Query Editor window, type the below SQL command:
RESTORE DATABASE database14
FROM DISK = 'Z:\SQLServerBackups\database14.bak' ;
Now, try to connect with the database and see if the issue is resolved. If the backup is obsolete or unavailable, then follow the next method.
Method 2: Run DBCC CHECKDB with REPAIR ALLOW DATA LOSS Option
If the reason behind the suspect mode is corruption in the database file, then you can use the DBCC CHECKDB command to repair the corrupt database. Follow the below stepwise instructions to repair the database using the DBCC CHECKDB command and bring it back online:
Note: The DBCC CHECKDB command with REPAIR ALLOW DATA LOSS option can cause data loss. So, it is recommended to create a backup of the SQL database before executing it.
- In SSMS, click New Query.
- In the Query Editor window, type the below command to turn off the suspect flag from the database.
EXEC sp_resetstatus database14;
- Next, you need to set the database to Emergency mode. Here's the command to do so:
- DATABASE database14 SET EMERGENCY
- Now, you need to check the integrity/consistency of the database. Run the DBCC CHECKDB command as given below:
- The command will display consistency errors (if any) in the database and suggest the repair options. You need to execute the repair commands.
- Before repairing the database, you need to set the database into Single User mode. Here’s the command:
- DATABASE database14 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Note: The option WITH ROLLBACK IMMEDIATE reverses the incomplete transactions and free the resources.
- Next, run the DBCC CHECKDB command with the REPAIR ALLOW DATA LOSS option.
DBCC CHECKDB(database14,REPAIR_ALLOW_DATA_LOSS)
- After successful execution of the above command, change the database from single user mode to multi-user mode. Here’s the command:
- Now, change the EMERGENCY mode to ONLINE mode by using the below command:
- DATABASE database14 SET ONLINE
- Refresh the database server. Now, you can connect to the database.
If the DBCC CHECKDB command doesn't work or to avoid data loss, you can use a professional SQL database repair software, such as Stellar Repair for MS SQL.
It is an MVP-recommended software to repair corrupt SQL database and recover all the data with complete integrity. This can help you restore the database from suspect mode.
The software can repair primary and secondary database files with complete precision. It can help fix common SQL database corruption errors. You can download the software's demo version to scan the corrupt database file and preview the recoverable objects.
Conclusion
If the SQL database is in suspect mode, you will not be able to connect to the database. Above, we have discussed the methods to recover the database from suspect mode. If you have a healthy backup, you can recover the database from backup.
If backup is not available, you can use the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to repair the SQL database.
However, running this command may result in data loss. Alternatively, you can use a professional SQL database repair software, like Stellar Repair for MS SQL. It can repair corrupt SQL database (MDF/NDF) files and restore all the data with complete integrity.
