Database in Recovery Pending State

  • shankar.gurav

    SSC Journeyman

    Points: 75

  • dwe71

    Old Hand

    Points: 344

    Thanks a lot - restarting the service worked for me. Still no idea why this happened, unless maybe I forgot to detach the database last time I opened SQL Server Management Studio, and/or used it in database view in Visual Studio in the meantime.

    The service name for me was "MSSQL$SQLEXPRESS", with a display name "SQL Server (SQLEXPRESS)".

    Cheers

    David.

  • ericjorg

    SSChasing Mays

    Points: 620

    I had this issue on Sql 2012 and fixed it this way:

    1) Stop SQL

    2) Delete the recovering database's log file

    3) Start SQL

  • Gail Shaw

    SSC Guru

    Points: 1004474

    ericjorg (4/9/2013)


    I had this issue on Sql 2012 and fixed it this way:

    1) Stop SQL

    2) Delete the recovering database's log file

    3) Start SQL

    Good way to destroy a database...

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ericjorg

    SSChasing Mays

    Points: 620

    GilaMonster (4/9/2013)


    Good way to destroy a database...

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Yes, that is exactly right. In my case, this was a test database and the logfile was created in a different version of SQL server, so it was safe to wipe it out.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    ericjorg (4/9/2013)


    GilaMonster (4/9/2013)


    Good way to destroy a database...

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Yes, that is exactly right. In my case, this was a test database and the logfile was created in a different version of SQL server, so it was safe to wipe it out.

    Very exceptional situation then. Normally deleting the log of a database that's in recovery will make the database recovery_pending, deleting the log of a database that's recovery pending will result in it still being in the recovery pending state.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • soorajsahadev

    SSC Journeyman

    Points: 85

    Hi all,

    My database is showing as RECOVERY_PENDING any one can help me ?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please post your question in a new thread and include as much detail as possible about what events preceded the problem and what errors are in the error log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • dg81328

    SSC Veteran

    Points: 267

    check the permssions on the database files after confirming they exist in the location expected. you may find that the permission has been damaged. Re-add the SQL Server service account to the file/security with full rights, run the offline/online commands and it should come onlin.

  • Pyman

    SSChampion

    Points: 13830

    Check for the location where your ldf and mdf files are and As long as you have them try doing a detach/attach and it should bring the database to a normal state.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Sapen (7/11/2013)


    Check for the location where your ldf and mdf files are and As long as you have them try doing a detach/attach and it should bring the database to a normal state.

    No.

    A database that is in any suspect/recovery_pending status should not be detached. A database so detached may well not reattach, meaning you've gone from a database in the recovery_pending state that you may be able to fis, to a bunch of files in the file system that you can't do much with.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Terence Keys

    SSC Eights!

    Points: 876

    By running the command in your post I found out that my .mdf and .ldf files were read only so it did help me.

    Thank you!

  • dyun

    SSC-Addicted

    Points: 434

    Hi Gail,

    i too am facing the same issue that three of my databases are in 'recovery pending' mode. I have checked the luns that host these database, they are all up and running normally with sufficient disk spaces. the datafile and logfile has not been changed since last month. Do you have any suggestions?

    regards,

    danny

  • dwe71

    Old Hand

    Points: 344

    Did you try restarting the SQL Server Agent service under control panel > administrative tools > services ?

    Worked for me.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    .

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 68 total)

You must be logged in to reply to this topic. Login to reply