How to Remove Database from Suspect Mode in SQL Server

News Knowledge Base
1

How to Remove Database from Suspect Mode in SQL Server

Scenario: There are times when you try to access your database, and find out it is gone into the suspect mode which makes your data inaccessible, also restricts you to perform any transactions.

Root cause:Whenever SQL Server finds any database suspicious, it marks it as “Suspect” and puts database into suspect mode. There can be several reasons behind any database getting suspicious.

  1. Any kind of corruption detected by SQL Server in the database file.
  2. If the hard disk is running out of space.
  3. Due to missing / corrupted log files.
  4. If SQL is not able to execute the roll back or roll forward operation.
  5. If Hardware or software database corruption has occurred in SQL server.
  6. Any kind of virus attacks.
  7. Sudden shut down of the system while the SQL server is running some task.

Note: As a fact, it is seen that there are 85% chances of database going into suspect mode due to improper shutdown.

Solution: While the issue happened, we need to professional DBA team get involved to solve the issue. Based on experience and practice, there is a manual approach to recover MS SQL from Suspect Mode:

Step-1. Turn off the suspect flag and Set the status of your database to Emergency mode. This is the first step to start learning how to remove database from suspect mode in SQL server.

Syntax: EXEC sp_resetstatus ‘db_name’;

ALTER DATABASE Database Name SET EMERGENCY

Step-2. Now look for any inconsistencies via DBCC CHECKDB(”); command.

Syntax: DBCC checkdb(‘DatabaseName’)

Step-3. Now Bring the database into Single User Mode and Rollback the previous Transactions.

Syntax: ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step-4. Now Take the complete backup of the database.

Step-5. Repair the database with DBCC CHECKDB Repair Allow data loss command.

Syntax: DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

Step-6. Now bring the database to Multi-user mode.

Syntax: ALTER DATABASE database_name SET MULTI_USER

Note: In case of data loss the user can restore from the backup available(Step 4). And update the connection string in the UiPath.Orchestrator.dll.config file, restart the IIS to make the change valid.

Tips to prevent SQL Database from Suspect Mode:

  1. Always keep your hard disk free in space. A full disk space tends to be the major reason behind causing corruption in the database files.
  2. Make a habit to create backups every now and then.
  3. Make sure to properly shut down your system.
  4. Always keep a check on your log files.
2

Great insights! I understand that dealing with a database in suspect mode can be stressful, and your step-by-step approach is really helpful. In my experience, relying on REPAIR_ALLOW_DATA_LOSS can be risky as it may lead to data loss. If backups aren’t available or the corruption is severe, using a trusted tool like Stellar Repair for MS SQL can be a safer option. It can repair corrupted MDF and NDF files without data loss, ensuring database integrity. Thanks for sharing these best practices!