Tuesday, October 30, 2012

How to set SQL Server Database 'Recovery pending state' in to 'Online state'


In case database is showing in 'Recovery Pending state, it means that recovery cannot be started. Until the cause is get fixed, recovery cannot run and the database cannot come online. You can check the state of the database by using sys.databases catalog view. Here are many reasons of this SQL Server behavior, the most common one is the log file is missing or corrupt. The other ones are:


  • MDF file get corrupted.
  • No enough space available for the SQL Server to recover the database during startup.
  • Database cannot be opened due to inaccessible files or insufficient memory or disk space.
  • Database files are being held by operating system. 
  • Sudden SQL Server Shutdown, Power failure or a Hardware failure.


First you should check following two points:

1.First you should check that MDF & LDF file is exist or not? 
2.Is Drive is available? 

If so then you should drop the DB and restore from backup. If you don't have backup then use following steps to resolve the issue:

  • you should try stopping SQL Service
  • Free up some space, make sure there's enough space there for the recovery to complete
  • Now Restart the SQL service to get back SQL database online again. 


After using all following steps if you are unable to resolve this error, it means that the error occurs due to log file corruption. In this case, you have last resort that is Switch the database to emergency mode, then to single user mode, run DBCC CHECKDB with the repair_allow_data_loss option. This command will lose data & have chances that may not work. If it doesn't work, there are no further alternatives, so try this command when no other options are available. 

Hope the article will help you to get back your database in online state!!

5 comments:

  1. Hello,

    I have the same problem with one of my database. The size of the database is around 8 GB.
    Whenever its get into recovery pending state, I used to run this following script and its working fine.

    USE master
    GO
    ALTER DATABASE ALPHADOC
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    GO
    ALTER DATABASE ALPHADOC
    SET READ_WRITE
    GO
    ALTER DATABASE ALPHADOC
    SET MULTI_USER
    GO

    But I need to find a permanent solution, because my users are not aware about sql script or any database activity and all things.
    So I don’t want to involve them in to this point. Guys, please give me a solution for the same.

    How to stop this or how to find the exact reason

    Thanks
    Ribu



    ReplyDelete
  2. I had the same problem and the following command brought the database back online

    Alter database set online

    ReplyDelete
  3. Thank you so much for the blog.I had the similar issue.I restarted the services.It worked fine after that.. :)
    thanks a ton all.. :)

    ReplyDelete
    Replies
    1. Thank you so much .I had similar problem . I restart the my service and get well.....

      Delete
  4. You can also try to detach & attach the database.

    ReplyDelete