Encountering a database stuck in recovery within SQL Server can be concerning. You might find messages in your SQL Server Error Log similar to this:
Recovery of database ‘
{Database Name}‘ ({Database ID}) is{N}% complete (approximately{N}seconds remain). Phase{N}of 3. This is an informational message only. No user action is required
This message, particularly concerning your SQL Server database in recovery, indicates that your database is undergoing crash recovery. This process is a standard procedure SQL Server initiates after an unexpected shutdown or restart where the database wasn’t cleanly closed. It’s crucial for maintaining database consistency and data integrity.
Why SQL Server Database Recovery Happens
When a SQL Server instance restarts without a clean database shutdown, the system automatically triggers crash recovery. This is essential because SQL Server needs to ensure that all database transactions are in a consistent state. Transactions are recorded in the transaction log, guaranteeing durability. However, data modifications are initially performed in memory and later written to the physical data files asynchronously via a process called checkpoint. This asynchronous nature is why an unclean shutdown necessitates a recovery process upon startup.
The recovery process in SQL Server is structured into three distinct phases, each involving a pass through the transaction log to bring the database back to a consistent and online state.
- Analysis Phase
- Redo Phase (Roll Forward)
- Undo Phase (Rollback)
Let’s delve into each phase to understand what SQL Server is doing when your database is in recovery.
1. Analysis Phase: Examining the Transaction Log
The initial phase of SQL Server database recovery is the Analysis Phase. During this stage, SQL Server meticulously scans the transaction log. The primary goal here is to determine the current state of the database and identify the operations required to bring it to a consistent state.
The analysis phase identifies:
- The location of the most recent successful checkpoint. Checkpoints are critical points in time where all committed changes are written to the data files.
- Transactions that were in progress at the time of the unexpected shutdown. These transactions might need to be rolled forward (redo) or rolled back (undo) to ensure data integrity.
Essentially, the Analysis Phase is a reconnaissance mission, setting the stage for the subsequent recovery operations.
2. Redo Phase: Rolling Forward Committed Transactions
The second phase, Redo, also known as Roll Forward, focuses on ensuring that all committed transactions are reflected in the database files. If a transaction was committed and recorded in the transaction log but the changes weren’t yet written to the data files due to the asynchronous nature of checkpoints, the Redo phase takes care of this.
In this phase, SQL Server re-applies or “redos” the operations of all transactions committed after the last checkpoint. This guarantees that no committed data is lost, even if it was only in memory at the time of the crash.
For users of SQL Server Enterprise Edition, a feature called Fast Recovery can allow the database to become accessible online immediately after the Redo phase is complete. However, in other editions, the database remains unavailable until the Undo phase is also finished.
3. Undo Phase: Rolling Back Incomplete Transactions
The final phase is Undo, or Rollback. This phase addresses transactions that were either explicitly rolled back or were still active and uncommitted when the SQL Server database experienced the unclean shutdown. To maintain the ACID properties of the database, specifically atomicity and consistency, these incomplete changes must be reversed.
During the Undo phase, SQL Server processes the transaction log to identify and undo any changes made by transactions that were not fully committed at the time of the crash. This ensures that the database returns to a consistent state, as if the incomplete transactions never occurred.
What to Do When Your SQL Server DB is in Recovery
When you notice your SQL Server database in recovery, the most important action is often inaction. Attempts to force the database online using commands like:
RESTORE DATABASE YourDatabaseName WITH RECOVERY;will typically fail. SQL Server is already actively performing the recovery process. Executing RESTORE...WITH RECOVERY; would essentially instruct SQL Server to restart the very recovery process that’s already underway.
Patience is Key
The best course of action is simply to be patient. The informational message in the error log explicitly states:
No user action is required
Pay close attention to this part of the message. While the log message includes an estimated time remaining, it’s important to know that this estimate is often unreliable. It can fluctuate, sometimes even increasing, and may not accurately reflect the actual time to completion. In many cases, the recovery process might finish much quicker than initially estimated.
Avoid Rebuilding the Transaction Log
It might be tempting to try and bypass the recovery process, perhaps by attempting to “throw away” the transaction log and start fresh. Techniques like attaching a database using ATTACH_REBUILD_LOG are available, but strongly discouraged, especially for production databases.
For instance, attempting to attach a database without a log and rebuild it using a command like:
CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:PathToYourDatabase.mdf') FOR ATTACH_REBUILD_LOG;on a database in recovery could lead to errors such as:
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. Msg 1813, Level 16, State 2, Line 5 Could not open new database ‘YourDatabaseName’. CREATE DATABASE is aborted.
This error indicates that SQL Server cannot rebuild the log due to the database’s state at the time of shutdown. In such scenarios, you will be forced to rely on the original transaction log and allow the standard recovery process to complete. Waiting for the recovery to finish is almost always the safest and most effective solution to bring your SQL Server database back online consistently.
