none
Database In Recovery every time I restart SQL Server service

    Question

  • I have a database that goes into recovery whenever the server is rebooted or the SQL Server service stopped and restarted.

    I dont' seem to lose any data so it is more of an annoyance than anything else.  The database is quite big (32GB) and takes 10-15 minutes to recover each time.

    I have loads of disk space, I stop SQL Agent first so no jobs are running, I make sure no users are connected to the database before bringing the server down.  Am I missing something here?  Should I close the database before restarting the service?  The problem then is what happens when the server crashes/ reboots for maintenance?

    Is there any way to find out WHY the database went into recovery?  The Application log just shows the database restarting and no hint of any problems.

    This is making me very paranoid about the integrity of my database and so I keep taking backups of it in case I lose a lot of work.

     

    Thursday, February 17, 2011 2:12 PM

Answers

  • This is normal and expected behavior. 

    The time it takes to "recover" is largly dependent on the size and number of transactions in your log file.  You should be doing regular log file backups which will trim the log file and reduce the time it takes.

    • Marked as answer by Diksta Thursday, February 17, 2011 3:21 PM
    Thursday, February 17, 2011 2:35 PM

All replies

  • Can you describe in more detail what do you mean by database goes into recovery?


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Thursday, February 17, 2011 2:20 PM
  • 1) Stop SQL Server service

    2) Deleted .mdf/.ldf files belong to that database

    3) Start SQL Server service

    4)Restore that database from the last good backup (I just hope you have one)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 17, 2011 2:28 PM
  • Thanks Uri... I do have a good backup so I will try that shortly.

    @Krystian Zieja
    http://support.microsoft.com/kb/820835

    I know this doesn't explain exactly what a database "In Recovery" means but it is basically when you find a database has disappeared, i.e. you can't connect to it, you expand the Database node and see it has "(In Recovery)" after the name.

    Thursday, February 17, 2011 2:35 PM
  • This is normal and expected behavior. 

    The time it takes to "recover" is largly dependent on the size and number of transactions in your log file.  You should be doing regular log file backups which will trim the log file and reduce the time it takes.

    • Marked as answer by Diksta Thursday, February 17, 2011 3:21 PM
    Thursday, February 17, 2011 2:35 PM
  • Okay, I have my database in Simple logging mode and I just shrank the LDF from 12GB to 1MB!

    Maybe this was the cause of my problems?

    Thursday, February 17, 2011 2:49 PM
  • Yes, that could explain why it took long time for recovering....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 17, 2011 3:03 PM
  • Yes, that could explain why it took long time for recovering....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 17, 2011 3:03 PM
  • Wow - I deiniftely do not recommend deleting and restoring the database for this. My guess is that you have lots and lots of Virtual Log files inside your ldf file. I touched on the subject here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp, with link to here: http://blogs.msdn.com/b/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, February 17, 2011 5:51 PM
  • Dude - I can't believe you commented on my thread... you are one of my SQL heroes for the article you wrote about DateTime types in SQL Server!!

    Yes, I read your blog about this a while back and was just looking to see if I could find it again - many thanks for the link.

    The problem is that this is a Test database and every now and then I run a script to drop all the tables and create it again.  This results in a huge log file that ends up 99% unused.  I will read your article again and see if this helps with my situation.

    Thursday, February 17, 2011 5:59 PM