We have a very large DB (6TB) that is taking a long time in recovery, after we rebooted the server.
After doing some research we understand that it is because of the large number of VLFs. Right now, it is a waiting game.
In the SQL Server log, we see that the ETA is 24 hours! This is for the first of the three phases!
We have announced everyone that DB will be available in 24 hours, not knowing there are 2 more phases.
Does anyone know, how long the other 2 phases take? Just a ballbark number would do. By any chance are the next 2 phases shorter than the first one?
Best Answer
Here's what we found:
Even though the SQL LOG claimed an ETA of 24 hours, the DB recovered in 12 hours. So the numbers were grossly over-estimated.
Even though SQL LOG was showing percentage recovery completion, it didn't go all the way up to 100%. At around 27%, SQL log said everything okay and recovery complete.
In our case 2 things caused this issue:
The answer to my original question: It seems the phase 1 is the slowest; 2 and 3 went very quickly.
We got an answer from Microsoft. Here are some notes from them:
This might look counteractive; I'm going to post it anyway. If I have to refer in the future, or for folks who might be a victim of this scenario.
It took 13 hours to recover the db. Here are the rough numbers.
Total Recovery time (about 13 hours) = 11 hours analysis + 1 hour redo + 0 hours undo