How to remove recovery pending state in SQL Server?

by
How to remove recovery pending state in SQL Server

Recovery pending is a state in SQL Server that occurs when a database is recovering after a crash or failure. This prevents access to the database until the recovery process completes. There are a few methods to resolve a stuck recovery pending state and regain access to your database.

What causes the recovery pending state in SQL Server?

The main causes of recovery pending state are:

  • Server crash or failure while write transactions were in progress. This leaves the transaction log in an inconsistent state.
  • Hardware problems like disk failures. SQL Server cannot access database files to complete recovery.
  • Incorrect backup and restore sequences. This can result in orphaned transaction logs.
  • Database corruption. Damaged system tables can prevent completion of recovery.
  • Blocking processes during startup. External processes like antivirus software can block file access.

When SQL Server restarts after such failures, it detects inconsistencies and uncommitted transactions in the transaction log. It puts the database in recovery pending mode to redo the unfinished transactions and bring the database to a consistent state.

How to check if a database is in recovery pending state?

To check if a database is stuck in recovery pending mode in SQL Server, query the sys.databases system view:

SELECT name, state_desc FROM sys.databases;

If the state_desc column shows ‘RECOVERY PENDING’ for a database, it is stuck in recovery mode.

You can also check the SQL Server error log for messages like:

Database 'mydatabase' is marked RESTORING and is in a state that does not allow recovery to be run.

Methods to resolve recovery pending state

1. Restart SQL Server

The simplest method is to restart the SQL Server service. The restart clears any pending transactions and locks, allowing the recovery process to finish. This works if the pending state was caused by temporary glitches or blocking processes during startup.

To restart SQL Server:

  1. Open SQL Server Configuration Manager
  2. Right-click on the SQL Server service and select Restart
  3. Wait for service restart to complete and check if database state is now ONLINE

2. Rebuild transaction log

If restarting the service does not work, the transaction log file may be damaged and need rebuilding. This can be done by:

  1. Setting the database to EMERGENCY mode using ALTER DATABASE
  2. Backing up the transaction log to create a new log file
  3. Taking a full database backup
  4. Restoring the database, which rebuilds the log

Example commands:

ALTER DATABASE mydb SET EMERGENCY;  
BACKUP LOG mydb TO disk='log.bak';
BACKUP DATABASE mydb TO disk='full.bak';
RESTORE DATABASE mydb FROM disk='full.bak' WITH RECOVERY;

3. Move database files to new location

If the database files themselves are damaged and preventing recovery, moving the database files can help. This involves:

  1. Detaching the database
  2. Manually moving the data and log files to a new folder
  3. Attaching the database from the new file location
USE master;  
GO
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'mydb';

-- Move files to new location

CREATE DATABASE mydb 
ON (FILENAME = 'C:\data\mydb.mdf'),
(FILENAME = 'C:\data\mydb.ldf')
FOR ATTACH;
GO

4. Delete corrupted files

If individual database files are corrupted, they can be deleted to allow SQL Server to recreate them. This involves:

  1. Detaching the database
  2. Deleting the corrupted data or log files
  3. Attaching the database, which recreates missing files
USE master;
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'mydb'; 

-- Delete corrupted data file

CREATE DATABASE mydb ON 
(FILENAME = 'C:\data\mydb.mdf'),
(FILENAME = 'C:\data\mydb.ldf')
FOR ATTACH;

5. Start database in emergency mode

Starting a database in emergency mode can bypass recovery errors and allow access to database. It involves:

  1. Starting SQL Server in minimal configuration mode using -f flag
  2. Setting database to EMERGENCY mode
  3. Accessing database to troubleshoot and repair issues
-- Start SQL Server in minimal configuration
NET START MSSQLSERVER /f 

USE master; 
ALTER DATABASE mydb SET EMERGENCY;

-- Can now access database to diagnose problems

Advanced recovery options

If the basic recovery methods don’t work, you can try more advanced options like:

  • Piecemeal restores – Gradually restore backups one at a time
  • Page repairs – Repair corrupt pages identified using DBCC CHECKDB
  • Replay transaction log – Manually replay log on top of backups
  • Partial database availability – Bring parts of database online

It’s best to involve experienced DBAs and Microsoft Support for advanced recovery techniques. They can diagnose complex issues like database corruption, malformed logs, and catalog inconsistencies that block recovery.

Preventing recovery pending state

Here are some best practices to avoid recovery issues:

  • Use a sound backup strategy with frequent log backups
  • Enable database mirroring for higher availability
  • Enable auto-closing of database to avoid orphaned transaction logs
  • Monitor disk health and free space for data files
  • Apply latest SQL Server updates and service packs
  • Restrict access to production databases to essential roles

Conclusion

Recovery pending state prevents access to SQL Server databases after a failure. Quick fixes like restarting SQL Server or rebuilding the log may resolve it. For persistent issues, you may need to repair damaged files, restore from backup, or use advanced recovery methods. Following best practices for backups, high availability and monitoring can help avoid recovery states.