11

I restored a 35Gb database on my dev machine yesterday and it was all going fine until this morning when my client app couldn't connect. So I opened SQL Management Studio to find the database 'In Recovery'.

I don't know a huge amount about this other than it is usually something to do with uncommitted transactions. Now since i know there aren't any uncommitted transactions it must be something else. So first off, I'd like to know under what conditions this can happen. Secondly, while this is going on I can't work so if there are any ways of either stopping the recovery, speeding it up or at least finding roughly how long it's gonna be that would help.

Joel
  • 4,732
  • 9
  • 39
  • 54
EightyOne Unite
  • 11,665
  • 14
  • 79
  • 105
  • In my experience I saw the same when the server crashed (by OS error or by power interruption) for big database (about 100 GB). Did you find in the event viewer some error or warning? The only way to stop the recovery is shutdown the sql server, but when the server restart...the recovery start again! – lg. Sep 23 '09 at 08:50

2 Answers2

14

Do not shut down SQL while recovery is in progress. Let it finish. Check the error logs. If it doesn't finish, restore from backup.

  • 1
    Check out this as well: http://www.sqlskills.com/BLOGS/PAUL/category/Bad-Advice.aspx –  Sep 23 '09 at 10:35
  • well it's not really an answer but it's the best i got it seems. Strange thing is i did just that and ran `dbcc checkdb` straight after and there was absolutely nothing wrong. Hasn't happened since either. Who knows what was up! – EightyOne Unite Sep 24 '09 at 10:59
  • 2
    It could have been anything, then. Recovery usually happens when the MSSQL service doesn't terminate correctly, and databases are not checkpointed, so all logs are replayed. If you had a particularly long-running transaction at the stage that the server died, it would have to reprocess it. –  Sep 25 '09 at 07:44
  • 4
    Be sure to refresh the DB yourself, as Microsoft can let you stare at your screen forever, even though the process has finished hours ago.. – ofirbt Oct 28 '13 at 08:12
  • Sure, that was implied by the "check the error logs", which you can also get to in Management Studio. –  Oct 29 '13 at 15:24
11

You can find out how long it's going to take by looking in the event viewer. In the Application section on the Windows Logs you should get information messages from MSSQLSERVER with EventID 3450 telling you what it's up to. Something like:

Recovery of database 'XYZ' is 10% complete (approximately 123456 seconds remain) etc etc

I'm afraid I don't know how to stop it (yet).

Julian Jelfs
  • 473
  • 6
  • 8