Reason that might cause Database became In Recovery in SQL Server 2008

  • Hi All,

    I have a problem in SQL Server 2008 R2.

    I was running a query that updated huge data in database, therefore it takes a lot of time to finish.

    And before the query has been finished (after 1 hour processing), I got error message "Cannot open database "XXX" requested by the login. The login failed". And then I tried to connect to that database again, and the Database became XXX(In Recovery). After 5 hours, the database has recovered on its own.

    Does anybody know what is the problem that might cause this issues(database became In Recovery)?

    Thanks in advance

  • SQL was restarted and a large transaction had to either roll back or roll forward.

    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
  • Hi Gail,

    I was also stuck in similar scenario, and my database went into DBName(Recovery) for a short time and then in backgorund it took 16 hour for the rollback part to complete.

    I just wanted to discuss Fast Recovery Concept of SQL Server. As long as the database is showing (in Recovery) in Management Studio it is Roll-Forward going on. And after that Rollback happens in background.

    Moral of the Story:- Never Restart your server while a heavy transaction is going on. And if you did make sure you are watching Error Log to give some reason about slow performance.

    Thanks

  • RakeshRSingh (6/1/2011)


    I just wanted to discuss Fast Recovery Concept of SQL Server. As long as the database is showing (in Recovery) in Management Studio it is Roll-Forward going on. And after that Rollback happens in background.

    Enterprise edition only. All other editions the rollback is offline too.

    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
  • Just FYI, we had a guy who had autoshrink and autoclose to on which also cause a similar issue (db went in and out recovery without any obvious reason and it caused a lot of troubles).

  • Thanks all.

    That's because I used virtual Machine and the database is very huge.

  • ratna_ain (7/8/2011)


    Thanks all.

    That's because I used virtual Machine and the database is very huge.

    I agree with the huge db part but virtualized machine, when well configured shouldn't be a massive factor here...

  • Yes SSCoach, may be there is a problem with the configuration of the virtual machine.

    Because when I run same query to the same database in the physical server, it takes less time to finish than in that virtual machine.

    Thank you

  • ratna_ain (7/13/2011)


    Yes SSCoach, may be there is a problem with the configuration of the virtual machine.

    Because when I run same query to the same database in the physical server, it takes less time to finish than in that virtual machine.

    Thank you

    I also use virtual machines and so far I can't complain about the performance. Did you make sure all the resources are being comnitted to the virtual machine and that your host is not overcommitting?

    Check this article by Brent Ozar.

  • Hi,

    While updating the data in the table you have lost the connectivity.You can check the Logs,there was a connection lost error.

    To overcome the recovery problem always shrink you log files.

    Gaurav and Suneet.

  • suneet.tg (6/18/2013)


    To overcome the recovery problem always shrink you log files.

    No you can't. If the DB is in recovery the log can't be shrunk. Once the DB comes online the log shouldn't be shrunk. Shrinking is not going to prevent a DB going into recovery and is generally a poor thing to do to the log.

    p.s. 2 year old thread.

    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
  • I was facing same problem once after shrinking log files(reducing VLFs) recovery is fast.

  • yes correct as Gail told enterprise edition took online recovery while restart the server..

    Standard Edition few months back i noticed that recovery on offline mode on Transaction log file 35 GB.. and tooks online 4 hours to become a online database.. so after online I did shrunk Log file upto 1 GB also configured Tlog backup every 30 mints then Tlog never grown more size.

Viewing 13 posts - 1 through 12 (of 12 total)

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