Most SQL users experience connection issues with their SQL databases as the database is in suspect mode.

Recovering a database in this case from suspect mode in SQL will require that you perform consistency checks and transactions rollback.

Here at Ibmi Media, we regularly help our Customers to fix SQL related issues on a regular basis as part of our Server Management Services.

In this context, we shall look into what causes this error and how to get rid of it.

What triggers suspect mode in SQL in relation to a database?

When a database is in suspect mode, it signifies that the recovery process of the database has started by did not finish successfully. To get your database to function as it should, you need to solve this issue and repair the corrupted files.

In a suspect mode, the users of the affected database cannot connect to the database to read and write data.

Some factors that makes a database to go into suspect mode are listed below;

i. As a result of a crash in the SQL Server.
ii. When Database files are not accessible.
iii. Failures in the SQL Server Database Operation.
iv. Unexpected Power Outage.
v. Inappropriate shutdown of the database or database server.
vi. Failure of the Hardware.
vii. When the database files are corrupted.
viii. Lack of disk space in the SQL Server when the database recovery process is ongoing and then fails.
ix. Insufficient memory or when Roll-back or Roll-forward operation cannot be performed.
x. When the database is inaccessible.
xi. typos in the database files drive during any maintenance activity.
xii. Due to antivirus prohibiting access of data while coming online.
xiii. Database being backed-up by third-party software.

Ways to recover the database from suspect mode in SQL?

To begin, it is important to analyze the database server to make it easy for the recovery and repairing process of the suspect database.

In some cases, files can go missing  or the location can change as a result of human error or after a maintenance activity.

This is why you need to check the database files to ensure that all the files are in the same location as it should be.

Once we are certain that there was no change, you can then bring the database online by running the command below;

Alter database DBNAME SET ONLINE

Here, "DBNAME" represents the name of the database that is affected and in suspect mode.

If this does not work, then you can run the T-SQL statement to know more about the error and what the database went into suspect mode.

Run the statement as shown below;

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

This will display more information about the errors. Then we can now proceed with the repair processes.

If the error is displaying data files missing then have a look at connectivity of the data file drive from storage side. You should also check if the data file drive is healthy or not.

Steps to solve SQL Server Database Suspect Mode Error?

To fix this error, follow the steps presented below;

1. Start by bringing the Database Online in "EMERGENCY MODE".
2. Then perform Consistency Check using DBCC Command "DBCC CHECKDB".
3. Next, bring the database in "SINGLE_USER" Mode to "ROLLBACK TRANSACTION".
4. Now take a full backup of the User Database which was marked Suspect Mode.
5. Then, run the statement "DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS". Note that this will lead to Data loss.
6. After the above command is executed successfully, the next thing to do is to bring the Database in "MULTI_USER" mode for normal read and write operations.

How to bring a Database Online in "EMERGENCY MODE"

A database in suspect mode cannot be accessed and connected to. The best approach is to bring the database in "EMERGENCY" mode to repair the database.
To do this, run the T-SQL statement as shown below;

USE master

GO

ALTER DATABASE DBNAME SET EMERGENCY

GO

How to perform Consistency Check Using DBCC Command "DBCC CHECKDB"?

Now that the database is in "EMERGENCY MODE", then you can query the database.
To do this, run the "DBCC CHECKDB" command as shown below to check the logical and physical integrity of all the objects within the specified database;

DBCC CHECKDB (BPO)

GO

How to bring the Database in "SINGLE_USER" Mode to "ROLLBACK TRANSACTION"?

Now you can bring the user database in SINGLE_USER mode by running the T-SQL code shown below;

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

Now, you can take a Full Backup of the User Database which was marked Suspect previously.

How to run DBCC CHECKDB WITH "REPAIR ALLOW DATA LOSS"?

Since the database  is in SINGLE_USER mode, then run the below TSQL code to repair the database;

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)

GO

As earlier stated, some data will be lost after using the "REPAIR_ALLOW_DATA_LOSS" option of DBCC CHECKDB command to repair the corrupted database.

How to bring the Database in "MULTI_USER" Mode?

To allow "MULTI_USER" access to the database, simply run the TSQL command shown below;

ALTER DATABASE BPO SET MULTI_USER

GO

This will restore the database for normal read and write operations.

Need support to recover a database from suspect mode in sql? We are available to help you today.