The status “recovery pending” in SQL Server indicates that a database is in the process of recovering after an unexpected shutdown or failure. This means that SQL Server is redoing committed transactions and undoing uncommitted transactions to bring the database back to a consistent state. The recovery process runs automatically when SQL Server is restarted after a crash or failure. “Recovery pending” will show as the database state until the recovery process is fully completed.
What Causes Recovery Pending State?
Some common scenarios that can lead to a SQL Server database entering the “recovery pending” state include:
- A power failure or server crash resulting in abrupt SQL Server shutdown
- A failover occurring, such as during a database mirroring failover
- Database recovery after a restore from backup
- An incorrect shutdown of the SQL Server service
In each case, SQL Server was not shut down properly and abruptly stopped while transactions were still active. When SQL Server is restarted, those transactions need to be recovered.
The recovery process runs in three stages:
- Analysis stage: SQL Server analyzes the transaction log to find out which transactions were active at the time of the failure or crash.
- Redo stage: SQL Server redoes all committed transactions that were recorded in the transaction log.
- Undo stage: SQL Server reverses any uncommitted transactions by rolling back the changes.
The database will remain in the “recovery pending” state until all three stages complete.
How Long Does Recovery Pending State Last?
The duration of the recovery pending state depends on:
- The number of active transactions at the time of the failure/shutdown
- The number of entries in the transaction log since the last log backup
- The recovery model of the database (FULL, BULK_LOGGED, SIMPLE)
- The hardware specifications, particularly memory, CPU and disk I/O speeds
Typically recovery pending can last anywhere from a few seconds to several hours for very large and busy databases.
For high-volume production databases, it is recommended to use the FULL recovery model along with frequent log backups. This limits how many log entries need to be processed during crash recovery and helps speed up the recovery pending duration.
Monitoring the Recovery Process
You can monitor the progress of a database going through recovery pending by checking a few SQL Server views and the error log:
sys.databases View
Querying the sys.databases view will show the state_desc column indicating “RECOVERY PENDING” for any database currently in recovery:
“`sql
SELECT name, state_desc FROM sys.databases WHERE state_desc = ‘RECOVERY PENDING’;
“`
This will reveal the databases waiting on recovery after a restart.
sys.database_recovery_status View
The sys.database_recovery_status view provides more granular info on the recovery stage for each database:
“`sql
SELECT
database_name,
recovery_state,
recovery_state_desc
FROM sys.database_recovery_status;
“`
The recovery_state values indicate the current phase:
- 1 = Restoring
- 2 = Recovery pending
- 3 = Online
- 4 = Recovering
- 5 = Standby
- 6 = Shutdown
Watch for databases in state 2 or 4 to be “recovery pending”.
Error Log File
The SQL Server error log will provide detailed messages tracking the start, progress and end of the recovery process for each database.
Error log message examples during recovery:
“`
Database ‘mydatabase’ is marked RESTORING and is in a state that does not allow recovery to be run.
Starting recovery for database mydatabase.
Phase 0: Initialization phase is beginning.
Phase 1: Restoring transaction log backup. …
Phase 2: Redoing changes recorded in the transaction log…
Phase 3: Undoing changes recorded in the transaction log…
Recovery for database mydatabase is complete.
“`
Forcing Database Recovery to Complete
In most cases it is best to allow the recovery process to complete on its own when a database is in the “recovery pending” state. However, in some situations it may be necessary to force recovery to finish more quickly. Some methods to make recovery complete faster:
Restart SQL Server Instance
Restarting the SQL Server service can help “reset” a stuck recovery process and force it to reinitialize. This may result in recovery completing faster on restart.
Force Recovery with Trace Flag 3608
Enabling trace flag 3608 instructs SQL Server to skip any remaining redo and undo operations and bring the database immediately online:
“`sql
ALTER DATABASE mydatabase SET EMERGENCY;
ALTER DATABASE mydatabase SET SINGLE_USER;
DBCC CHECKDB (mydatabase, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE mydatabase SET MULTI_USER;
“`
This bypasses any remaining recovery steps so use carefully only if data loss is acceptable.
Detach and Reattach Database
Another option is to detach the recovering database then reattach it. This starts recovery fresh from the beginning which may run faster than a stuck recovery.
Use this sequence of steps:
“`sql
USE master;
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db @dbname = N’mydatabase’;
— Physical file operations
EXEC sp_attach_db @dbname = N’mydatabase’;
“`
Again, this will discard any partially completed recovery work so only use if necessary.
Impact of Recovery Pending State
When a SQL Server database is in the “recovery pending” state, there are several impacts:
Database is Offline
No connections can be made to the database while it is in recovery pending status. Any client applications will be unable to log in and will receive an error like:
“`
Cannot open database “mydatabase” requested by the login. The database is in recovery mode.
“`
Normal operations cannot occur until recovery finishes successfully.
Other Databases Accessible
If multiple databases were on the SQL Server instance, any databases not undergoing recovery will remain online and accessible as normal. Only the database(s) specifically in recovery pending state will be offline.
Transaction Log is Inaccessible
The transaction log for the database cannot be backed up or truncated until recovery completes successfully. Any transaction log backup jobs will fail while the database is in recovery pending.
Blocked on Restores
No files can be restored onto a database during recovery pending. You must wait until recovery completes before running any RESTORE operations.
Blocked on DBCC Commands
Certain maintenance operations like DBCC CHECKDB and DBCC CHECKTABLE are blocked until recovery finishes and the database is online again.
Waiting for Recovery to Finish
In most situations, the best approach is to let recovery pending finish completely on its own without intervention. Forcing recovery to end prematurely can lead to data inconsistencies and loss.
To check when a database finishes recovery:
- Query sys.databases and sys.database_recovery_status until state/recovery_state indicates ONLINE
- Check error log for recovery finished message
- Try connecting to database with applications
Some guidelines for handling databases in recovery:
- Have patience and avoid forcing recovery completion unless absolutely needed
- Focus on restoring regular backups to minimize prolonged recovery times
- Consider failover or switching to secondary server if downtime must be minimized
- Communicate status to users so recovery can complete fully
With proper configuration, most database recovery scenarios can complete within a few minutes up to a few hours depending on traffic levels at time of crash.
Conclusion
Recovery pending is a normal database state in SQL Server indicating a database is offline while redo and undo operations are processing to recover after an improper shutdown or failure. The key points to remember include:
- “Recovery pending” is shown as database state during the recovery process
- Recovery redoes committed transactions and rolls back uncommitted ones
- Time to complete recovery depends on transaction log size and hardware
- Error log and system views provide recovery process monitoring
- Forcing recovery completion is possible but not recommended
- The database is unavailable during recovery pending
- Letting recovery finish fully ensures database consistency
Understanding the recovery pending state and the automatic recovery procedures of SQL Server helps ensure databases are restored to a usable state after crashes or failures. Careful configuration like full recovery model and frequent log backups will help minimize recovery times.
| Database Name | Recovery State | Recovery Status |
|---|---|---|
| MyDatabase1 | 2 | Recovery Pending |
| MyDatabase2 | 4 | Recovering |
| MyDatabase3 | 3 | Online |
