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:

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