SQL Server Database in suspect mode is a clear indication that you cannot access the database or no transactions are possible until it back to the online. You might have experienced that your SQL database is marked as SUSPECT. It happens due to several reasons: transactional log file of a database is corrupt, faulty hardware, missing transaction log file of the database, virus attack, improper shutdown of SQL server and much more. Look in the error log of your SQL server to know the specific cause for your database. Error log tells the exact cause why your database has gone into suspect mode.
In order to resolve this problem, you need to change the database status to EMERGENCY mode that provides a read-only access to the administrator. The basic purpose of changing the database mode to emergency is troubleshooting. For changing the database status to EMERGENCY mode, run the below T-SQL query:
ALTER DATABASE dbName SET EMERGENCY
Once you have changed the database status, now administrator can access it. Next step is DBCC CHECKDB execution. DBCC CHECKDB checks all logical and physical integrity of the specified database. If it finds any problem with the database then recommend appropriate repair options: repair_rebuild, repair_fast, or repair_allow_data_loss.
Next, run below T-SQL query to rollback any transaction and bring the SQL database into Single User mode.
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Now, Re-Run DBCC CHECKDB with repair options to repair the suspect database.
DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)
Note that this DBCC CHECKDB with repair_allow_data_loss will fix all the errors and repair them; however, it may cause data loss from the database.
If you find that the script runs successfully, bring the database back from single-user mode to multi-user mode. Run the following command to do it:
ALTER DATABASE dbName SET MULTI_USER
Suggestion: This is what you need to do to fix suspected database issue. Moreover, run DBCC CHECKDB command regularly to check for physical and logical integrity of the database.
Another workaround includes restoring the database from clean and updated backup that you have prepared before the problem occurs. It is the quick and best method to fix the problem.
However, you may face a situation when you do not have a most recent backup of database or backup is corrupt itself and database have been marked as 'SUSPECT'. In this case, an ultimate solution is to use Stellar Repair for MS SQL that is designed to recover database from the suspect mode, as well as repair, damaged or corrupt MS SQL Server database (.mdf & .ndf) files. It supports all latest version of SQL server including SQL server 2016.