Monday 29 October 2012

Cannot detach a suspect or recovery pending database

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.

No comments:

Post a Comment