Cannot detach a suspect or recovery pending database
January 23, 2012 1 Comment
After losing connectivity to the data and log volume for one of our SQL servers, we received the following error:
LogWriter: Operating system error 2 (failed to retrieve text for this error. Reason: 15100) encountered.
Which resulted in the database on that volume showing as being suspect.
After gaining connection back to the volume, I tried to detach the database, but received the following error:
Cannot detach a suspect or recovery pending database. It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)
To resolve i did the following:
ALTER DATABASE emergencydemo SET EMERGENCY; GO
EMERGENCY mode marks 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 emergencydemo set single_user GO DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; GO ALTER DATABASE emergencydemo set multi_user GO
This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.