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

-- Will take a long time depending on size of database.
DBCC CHECKDB (TCS)
GO

Sources:

Last modified: July 3, 2020

Author

Comments

Write a Reply or Comment