The SQL Server whenever it restarts all the databases goes into the recovery mode. In this state the database comes back in online mode and has a consistent state. It contains three sub phases within this process. Roll forward and rollback are the process contained in this phase. The detailed explanation for the above process is given below in details:
- Analysis: In this phase the SQL Server would go through the LDF file and build in-memory structures to know how much work is needed to be done in the next two phases.
- Roll Forward (Redo): At the time of shutdown of the database, there might be some transactions that are committed but are still not written to the MDF file via checkpoint.
- Rollback (Undo): In some states the transactions sometimes remains uncommitted and in order to bring the consistent state the database needs to be rolled back.
In this article we will discuss why SQL databse is in recovery mode and methods that the user should adopt in this recovery mode.
Reasons Why SQL Database Is In Recovery Mode
The reason why SQL Server database is in recovery mode is as follows:
- While Restarting the SQL Server.
- When the Database is Set Offline and Online.
- Restoring the database from backup.
All the above reasons mentioned are called Recovery process of the database and all the databases must go through these processes as explained earlier.
Methods to Be Performed when the Database Stuck in Recovery State :
First thing, always check for ERRORLOG. In Errorlog the first message displayed is:
Starting up Database ‘Customer’ (Name of the Database).
It means database files are opened and recovery process is started. After this the user will be able to see method 1like this:
Recovery of database ‘Customer’ (40) is 0% complete (approximately 37 seconds remain). Phase 1 of 3.
Recovery of database ‘Customer’ (40) is 3% complete (approximately 36 seconds remain). Phase 1 of 3.
After the method 1 is performed the recovery mode will go to method 2 and method 3 like this:
Recovery of database ‘Customer’ (40) is 3% complete (approximately 36 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 0% complete (approximately 142 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 7% complete (approximately 19 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 15% complete (approximately 26 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 21% complete (approximately 25 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 34% complete (approximately 20 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 41% complete (approximately 16 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 48% complete (approximately 14 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 55% complete (approximately 12 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 62% complete (approximately 10 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 75% complete (approximately 7 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 82% complete (approximately 5 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 88% complete (approximately 3 seconds remain). Phase 2 of 3.
Recovery of database ‘Customer’ (40) is 95% complete (approximately 1 seconds remain). Phase 3 of 3.
And once all the above three methods are performed the results will be shown as follows: 3807 transactions rolled forward in database ‘Customer’ (40).
0 transactions rolled back in database ‘Customer’ (40).
Recovery is writing a checkpoint in database ‘Customer’ (40).
Recovery completed for database Customer (database ID 40) in 30 second(s) (analysis 1409 ms, redo 29343 ms, undo 72 ms.)
This result shows the three phases of recovery mode in SQL Server explained earlier.
Conclusion
In this technical article we discussed why SQL database goes into recovery mode and what the user must do in this stage. Then, we described some methods to be performed during the time of the SQL recovery process. Hope, this might be helpful to all the SQL users. There are some cases in which the user is unable to recover the SQL database properly or stuck in recovery state. So it is recommend to use the utility like SQL Database Recovery which is able to quickly recover the SQL database and all its objects.
