Database stuck in Suspect Mode

Primary reasons when an SQL Server Database is marked in Suspect Mode

• System Issues
• Transaction Log File is Missing
• SQL Server Crash
• Database Files are inaccessible
• SQL Server Database Operation Failures
• Due to Improper shut down of SQL Server System
• Due to Sudden Power Outage
• Low Disk Space Issues
• Hardware Failure
• Drives missing or not mapped correctly

Fix:

Check the SSQL Server Logs for information about why the database is in Suspect Mode. For example: missing folder paths.

Make sure all servers in Always On have the same folder and drive structure. Fix the drives or folders and restart SQL Server.

USE master
GO

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO

-- Should be a fast process
ALTER DATABASE [MyDB01] SET EMERGENCY
GO

-- Should be a fast process
ALTER DATABASE [MyDB01] SET ONLINE
GO

If the above fails, do the following …

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

EXEC sp_resetstatus [MyDB01];

ALTER DATABASE [MyDB01] SET EMERGENCY

DBCC CheckDB ([MyDB01])

ALTER DATABASE [MyDB01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([MyDB01], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [MyDB01] SET MULTI_USER

Sources:

How to Repair Database in Suspect Mode in SQL Server