Bring Database Online From Restoring State
Summary:An SQL Server database moves between different states/modes. This article discusses SQL database stuck in "In Recovery" mode, and the reasons behind it. Also, explore the possible action you can take to bring the DB back to its consistent state.
After restarting SQL Server, the database moves to "In Recovery" mode. You may also see the DB in recovery state on its startup or when restoring it from backup.
Figure 1- SQL Database "In Recovery" Mode
The database 'recovering' state means that the database performs a recovery process and will automatically come online once the process is complete. However, you may experience that the recovery is slow, and the database is stuck in a recovery state. Your DB might still be under recovery state, as SQL databases undergo three phases of recovery, which can take time depending on your database files' size.
An Overview of Different Phases of SQL Database Recovery
Usually, when the database is not shut down properly on SQL Server restart, it undergoes crash recovery, ensuring that the DB remains consistent. There are three phases of recovery that an SQL DB needs to go through:
Phase 1 – Analysis
This phase starts from the "last checkpoint till the end of the transaction log." It creates a 'Dirty Page Table' (DPT) table that helps determine all the dirty pages at the time of the crash. Also, it creates an 'Active Transaction Table' (ATT) table to identify uncommitted transactions when the SQL Server stopped.
Phase 2 – Redo
In this phase, SQL Server rolls forward all the changes that happened after the checkpoint and before the crash. Essentially, in the redo phase, all the transactions which are committed but not yet written to the SQL data file (.mdf/.ldf) via checkpoint need to be rolled forward.
Phase 3 – Undo
If there were any uncommitted transactions at the time of database recovery, they have to be rolled back in the undo phase to bring the DB to a consistent state.
What to Do if Database Is Stuck in Recovery Mode?
Check the SQL Server Error Log to see the very first message in the database that may look similar to:
Starting up database 'DatabaseName'
This means that the DB files are opened, and the recovery process has started. Sometime later, you should see SQL Server undergoing 3 phases of recovery. Phase 1 of database recovery is shown below:
Recovery of database 'DatabaseName' (9) is 0% complete (approximately 95 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Recovery of database 'DatabaseName' (9) is 3% complete (approximately 90 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
After completion of phase 1, SQL Server will undergo phase 2 and 3 of recovery:
Recovery of database 'DatabaseName' (9) is 5% complete (approximately 85 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required…
Recovery of database 'DatabaseName' (9) is 95% complete (approximately 40 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Phase 3 of 3. This is an informational message only. No user action is required.
Once Phase 2 and 3 is complete, you will see something similar to:
3807 transactions rolled forward in database 'DatabaseName' (9). This is an informational message only. No user action is required.
0 transactions rolled back in database 'DatabaseName' (9). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'DatabaseName' (9). This is an informational message only. No user action is required.
Recovery completed for database DatabaseName (database ID 9) in 30 second(s) (analysis 1289 ms, redo 29343 ms, undo 72 ms.) This is an informational message only. No user action is required.
In the error log, pay attention to the message 'no user action is required'. This indicates that the database is in recovery state. However, the recovery may take longer than expected, and the database will be stuck in recovery mode.
Reasons Behind SQL Database Stuck in "In Recovery" Mode
Following are the reasons that may cause an SQL database stuck in recovery mode:
- A long-running transaction is rolling back
- Transaction log file size is huge
- Too many Virtual Log Files (VLFs) are inside the DB transaction log
- There was a bug in the SQL Server, which is now fixed.
What Can You Do to Bring Database Back to a Consistent State?
Workaround 1: Wait for the Database Recovery to Complete
The most obvious solution to bring the database back online is to be patient and wait for the recovery process to complete; this could take hours or days. If the recovery is taking too long than expected for a DB in an SQL Server 2008 or 2008 R2, applying Microsoft fixes may help.
Note: Avoid running the RESTORE command to bring DB online in a consistent state, as SQL Server is already attempting to perform the same task. And, running 'RESTORE with..Recovery' means putting the DB to go through the same steps again.
Workaround 2: Use a Professional SQL Database Repair Tool
If the recovery gets completed but fails to bring the database in a consistent state, using a specialized SQL repair tool such as Stellar Repair for MS SQL may help restore the DB to its original state.
Conclusion
This article discussed the SQL database stuck in the "In Recovery" mode issue and the three phases of DB recovery. It also outlined the reasons behind the database recovering state and explained workarounds you can try to bring the DB back online to a consistent state. But, if nothing works, using a third-party SQL database repair tool can come in handy.
Topics:
sql server, database, tutorial, sql, sql database recovery
Opinions expressed by DZone contributors are their own.
Bring Database Online From Restoring State
Source: https://dzone.com/articles/resolving-sql-database-stuck-in-recovery-mode