Search

Friday, March 16, 2012

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.

5 comments:

  1. Thank you very much sir..

    It helped me a lot...

    ReplyDelete
  2. May I add my thanks to that, worked first time

    ReplyDelete
  3. How long should I anticipate the Debugging Query to last on the first script? ALTER DATABASE DemoData SET EMERGENCY

    ReplyDelete
    Replies
    1. I could not understand the question. Please elaborate.

      Delete