Cannot detach a suspect or recovery pending database
January 23, 2012 5 Comments
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.
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!!
Hello Mohsin,
Your contribution certainly resolved my issue after hours of struggle. Thank you and god bless you.
Thank you Moshin, above steps resolved our issue as well. Thank you again.
Great. This has been a real nice help…Thank you so much….
That was a great Help. The steps, simple and effective, resolved our problem. Thanks once again.