Database in Suspect Mode

Yesterday I had mail to check the database health after the recovery of servers from Storage crash issue. I found one particular database was in 'Suspect' mode. Performed steps to fix this. 

Database will go to Suspect mode usually if there is any issue with Server Reboot, Storage issue, Database Corruption due to Network issue etc. In this case Storage SAN drive crashed, restored the storage from the Snapshot. 

1. Check the database state

select state_desc, * from sys.databases where state_desc<>'online'

2. Found database was in Suspect Mode. 

First step is to Restore the database from the latest Backup file. and Run the DBCC Integrity command to check it. Made sure everything fine on that.          

As a part of troubleshoot on the issue database.

               

            Brought the database to Emergency Mode, and do the DBCC Integrity check.

Use Master

go

ALTER DATABASE DBNAME SET EMERGENCY

DBCC CHECKDB (DBNAME )

3. Based on the DBSS Result\suggestion performed the DBCC REPAIR_ALLOW_DATA_LOSS.  Depending on the DBCC Results and suggestions 

Use Master

go

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

4. I was not able to access the database into single user mode, transaction was ending in the Deadlock. I changed the Deadlock priority and later was able to take the database into Single user mode.

SET DEADLOCK_PRIORITY HIGH 

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

or 

SET DEADLOCK_PRIORITY 10

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

5. Then performed the the DBCC REPAIR_ALLOW_DATA_LOSS. Database went to Recovery mode. DBCC Repair Allow data Loss should have fixed the issue by allowing some data loss. But it didn't fix the issue. Hence continued using the Restore database. Later I just dropped the database and renamed the Restored database to original name. 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBNAME'

GO

USE [master]

GO

/****** Object:  Database [DBNAME]    Script Date: 3/27/2019 12:29:38 PM ******/

DROP DATABASE [DBNAME]

GO