Users often are unable to connect to the SQL databases as the database is in suspect mode. To recover the database from suspect mode in sql, we may need to perform consistency checks and rollback of transactions.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What causes database to enter suspect mode in SQL?

A database in suspect state indicates that the database recovery process has initiated but not finished successfully. This indicates the users to fix that issue and repair the corrupted files.

While a database is in suspect mode, users won’t be able to connect the databases to read and write data.

There are many reasons that can cause a database to go in suspect mode. Some of them include:

• SQL Server Crash
• Database Files are inaccessible
• SQL Server Database Operation Failures
• Due to Sudden Power Outage
• Improper shutdown of the database or database server.
• Hardware Failure.
• Corruption in database files.
• There is not enough space available for the SQL Server to recover the database during startup.
• Inaccessible database files or Insufficient memory.
• Database files drive letters mistyped/changed during any maintenance work.

How to recover the database from suspect mode in SQL?

First step in recovering or repairing the suspect database is to analyze the database server.

Sometimes, files can go missing/changed to another location due to human error or maintenance work.

Thus, we need to check the files and make sure that all database files are in same location where they should be.

Once we have checked the drive and there is no change in it, then we can try to run below SQL statement to bring the database online.

Alter database DBNAME SET ONLINE.

Where, DBNAME is the name of the database name that is showing in suspect mode.

If it does’nt work, then we will run below t-sql code to get all errors due to which database went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once we have enough information about errors, then our next step would be Repair.

If error is showing data files missing, then check the data file drive connectivity from storage side. If it is missing, then check data file drive is healthy or not.

Steps to Fix the SQL Server Database Suspect Mode Error

1. Bring Database Online in EMERGENCY MODE
2. Perform Consistency Check Using DBCC Command DBCC CHECKDB
3. Bring the Database in SINGLE_USER Mode to ROLLBACK TRANSACTION
4. Take a Full Backup of the User Database which was marked Suspect Before
5. Execute DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS (Caution: It will result in Data Loss)
6. Once the above command has executed successful. Bring the Database in MULTI_USER Mode for normal read and write operations.

Bring Database Online in EMERGENCY MODE

When a database is in SUSPECT mode we will not be able to get connected to the database. Hence we need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.

USE master
GO

ALTER DATABASE DBNAME SET EMERGENCY
GO

Perform Consistency Check Using DBCC Command DBCC CHECKDB

Once the database is in EMERGENCY mode we will be able to query the database.

Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (BPO)
GO

Bring the Database in SINGLE_USER Mode to ROLLBACK TRANSACTION

Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Now take a Full Backup of the User Database which was marked Suspect Before.

Execute DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS

Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database.

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)
GO

A point to be noted here is that, when we repair the database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data.

Bring the Database in MULTI_USER Mode

Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE BPO SET MULTI_USER
GO

This will make the database available for normal read and write operations.

[Need any further assistance to recover the database from suspect mode in sql ? – We’re available 24*7]

Conclusion

In short, to recover a database from suspect mode in SQL we will need to perform a consistency check on database in emergency mode. Further we will need to run a repair with ALLOW DATA LOSS in single user mode. Today, we saw how our Support Engineers perform these tasks.