Cannot detach a suspect or recovery pending database - Không thể Detach hoặc recovery database đang bị pending

 

Cannot detach a suspect or recovery pending database

Today once the connectivity to the data and log volume for SQL servers is lost and I received the following error:


Log Writer: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.


This resulted in the database on that volume showing as being suspect.
I tried to detach the database, after connecting back to the Server, but got below error:


Cannot detach a suspect or recovery pending database.
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707) 


To resolve above error i did the following:
ALTER DATABASE DemoData SET EMERGENCY;
GO


EMERGENCY mode sets the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.


ALTER DATABASE  DemoData set single_user
GO
DBCC CHECKDB ( DemoData, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE  DemoData set multi_user
GO


This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.