none
SQL 2012 database stuck in Recovery Pending status RRS feed

  • Question

  • Hey all.  Thanks in advance to anybody that can please help me.  I have a SQL cluster which ran out of disk the other day.  I threw plenty more disk at the SAN.  However, 1 of the databases came back with Recovery Pending and has been over 24 hours.  I have tried to backup the database with no success.  I don't have a clean backup from the day it occured so would have a good amount of work missing.

    Running a dbcc check returns the following:

    Msg 945, Level 14, State 2, Line 1
    Database 'sqldb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    I have not tried a dbcc check with REPAIR_ALLOW_DATA_LOSS as there seems to be a high risk of losing whatever was going on.  Does anybody have an idea of how I can get the db back online?

    Saturday, January 12, 2013 10:39 PM

Answers

  • All,

    Though I appreciate all the feedback, I wound up watching a rather long video about recovery and corruption options.  Turns out the only answer to bringing the db back online was placing it into EMERGENCY mode.  Hopefully, it will help somebody later down the road.

    ALTER DATABASE sqldb SET EMERGENCY;
    GO
    ALTER DATABASE sqldb SET SINGLE_USER;
    GO
    DBCC CHECKDB (sqldb,REPAIR_ALLOW_DATA_LOSS)
    WITH NO_INFOMSGS;
    GO

    Monday, January 14, 2013 12:48 AM

All replies

  • detach - then re-attach will get you into more trouble.  Use Shageman0304 as the answer.


    R, J

    • Edited by Crakdkorn Tuesday, December 18, 2018 8:26 PM
    Saturday, January 12, 2013 11:30 PM
  • That didn't really help-----------------

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Attach database failed for Server 'USTAWOSSQL99\OS'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The transaction log for database 'sqldb' is full due to 'CHECKPOINT'.

    Could not open new database 'sqldb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9002)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Sunday, January 13, 2013 12:09 AM
  • In short, you may be in trouble.

    Have a look at

    http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

    The first answer given to your question was, IMHO, not a good one.

    Good luck.


    (Twitter | Blog)

    Sunday, January 13, 2013 1:41 AM
  • Just a note... the database is not in Suspect mode, it is in Recovery mode.  If there is a transaction in process, attaching and reattaching may or may not trigger the transaction to complete.    The answer would be different if the database were in suspect mode.  The fact that it is now in CHECKPOINT shows that there is an incomplete transaction.  Once back online, a backup can occur and the CHECKPOINT can resolve. http://support.microsoft.com/kb/110139?wa=wsignin1.0

    Seems to me this is more like a case where the database is in log ship mode.  In that case, the solution is relatively simple (this is not what needs to happen... it simulates what has happened):

    RESTORE DATABASE MyDB WITH RECOVERY

    USE master
    ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE
    ALTER DATABASE MyDB SET ONLINE
    RESTORE DATABASE MyDB WITH RECOVERY

    I don't believe you want to rollback anything though.  As you state above, the big trick is to bring it online.

    I think you can get it into Single User Mode and then from there, restore the diffs and the logs after backing up the tail, then restore the tail and put it back into multi-user mode.  Given the difference in opinion here, I am feeling a little gunshy.

    RESTORE DATABASE [MyDB]
    FROM DISK = 'c:\MyDB.BAK'
    WITH MOVE 'MyDB_Data' TO 'c:\data\MyDB.mdf',
    MOVE 'MyDB_Log' TO 'c:\data\MyDB_log.ldf' 


    R, J



    Sunday, January 13, 2013 2:06 AM
  •  

    Hi there,

    When your database is in recovery pending it do not allow any more transaction. simply it pause.

    Assume your database is in full recovery model .first thing take tail of the transaction log back up with no truncation.

    Restore your recent full backup with no recovery, restore your differential back up if you have. restore all transactional log backups with no recovery. Finally restore your tail of log back up with recovery.

    If your database is in simply recovery simply use ALTER DATABASE command to change the recovery model.

    Good luck.

    kumar

    Sunday, January 13, 2013 7:19 AM
  • have you tried to see if there is any pending MSDTC transactions? we have found that the db cannot be recovered if there was pending MSDTC and if the DB is involved in a database mirroring session.

    Monday, January 14, 2013 12:17 AM
  • All,

    Though I appreciate all the feedback, I wound up watching a rather long video about recovery and corruption options.  Turns out the only answer to bringing the db back online was placing it into EMERGENCY mode.  Hopefully, it will help somebody later down the road.

    ALTER DATABASE sqldb SET EMERGENCY;
    GO
    ALTER DATABASE sqldb SET SINGLE_USER;
    GO
    DBCC CHECKDB (sqldb,REPAIR_ALLOW_DATA_LOSS)
    WITH NO_INFOMSGS;
    GO

    Monday, January 14, 2013 12:48 AM
  • database recovery pending
    Friday, March 20, 2015 2:28 PM
  • Hi Everyone,

    Today I too gone through this situation, What I did I just restart my server and when it comes back I saw two of my database gone in Recovery Pending mode. I dig around and did so many things suggested on forums but no luck, I was concern for only one database and the other I never noticed in my database list I did try to detach the DB but no luck. Property of both database was not visible as both were in Recovery Pending mode.  

    I worked on the DB I was concern about, I made it offline and try to detach but it says access denied , file is in use by another process... After marking it Offline it was not allowing me to copy the file (mdf and ldf) for a safer side backup purpose. 

    Finally I mark both DB Offline and now system allowed me to copy and move files. After that I dropped both databases and attached mdf/ldf files, Surprised... I got database created with "Application_Log" name. Then I come to know that somehow while restarting the server a new database created with the name "Application_Log" and my original database was also there, since both Database are using same physical files both went in Recovery Pending mode (its my assumption). it took almost 3 hrs to get back to work. but finally I got my DB back I renamed it to the original name and everything looks fine now.

    but how it happened, is a mystery to me, may be some HDD issue!!

    Thanks

    Tuesday, July 14, 2015 9:30 PM
  • Did you look in the SQL Server errorlog for interesting messages that explains why the databases were in that state?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 14, 2015 9:48 PM