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.

Advertisements

3 Responses to Cannot detach a suspect or recovery pending database

  1. Adam Gorge says:

    If your database shows suspect & recovery pending state, it means your database got corrupted. If above solution get failed then you should use third party SQL Recovery Software to repair corrupt SQL Server database. Stellar Phoenix SQL Recovery is one of the best tool!!

  2. Anonynous says:

    Hello Mohsin,
    Your contribution certainly resolved my issue after hours of struggle. Thank you and god bless you.

  3. Anonymous says:

    Thank you Moshin, above steps resolved our issue as well. Thank you again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: