How To Fix SQL Server RECOVERY PENDING Mode

When your SQL database hits RECOVERY PENDING mode, don’t panic.

This status means recovery is stuck, not failed. To fix this, you need to find the root cause. It could be anything from adding more disk space to fixing or rebuilding database files.

Taking these steps can help restore your database to full functionality. This will ensure minimal downtime and protect data integrity.

SQL Server RECOVERY PENDING: What Does This Status Mean?

This means the database ran into a problem.

SQL Server knows it needs to run a recovery process on the database. However, something is stopping this process from running.

SQL Server doesn’t know if the recovery will fail. The recovery process just hasn’t started yet.

RECOVERY PENDING – the good news

The database is likely OK. It is less likely that it is damaged.

So don’t panic yet.

Be aware that the database will not be available to users. You will need to fix the error before bringing it back online.

How do you check the database state?

Microsoft SQL Server Databases are always in one specific state.

Possible database states are:

1. ONLINE
2. RESTORING
3. RECOVERING
4. RECOVERY PENDING
5. SUSPECT
6. EMERGENCY
7. OFFLINE
8. COPYING
9. OFFLINE SECONDARY

Run this query below and check the current state of your database:

SELECT name
	,state_desc
FROM sys.databases

Reasons why SQL Server Database state is in RECOVERY PENDING

There can be many. But here are a few I have seen:

1. SQL Server shut down due to failure. It could be hardware, power, or something else.
2. The disk could be missing.
3. MDF or LDF files might be missing.
4. The disk drive is full.
5. SQL Upgrade didn’t go well or was interrupted.
6. Big changes were being made, and something failed in the middle. This could include changes to the FILESTREAM feature.
7. The LDF (log) file has been corrupted.
8. The MDF database file can become corrupted for many reasons. It could be due to disk or SAN issues, hardware faults, viruses, or other problems.
9. The server restarted, and something changed. The disk or directory might be missing, or there could be a permissions issue.
10. Cryptoware has encrypted your SQL files.

What are the ways to fix the RECOVERY PENDING state?

Before starting any repair process, ensure you have sufficient backups of the database.

Start with looking at the SQL Server error log.

Is there anything that stands out and explains why DB is stuck?

If not, the next step is to check the Windows Event Viewer. Look for anything unusual or that stands out.

Method 1. How to fix RECOVERY PENDING mode – Set database state to ONLINE

If the DB recovery can’t start because there’s not enough disk space, follow these steps. This is a common issue:

1. Increase the disk space
2. Set the DB to ONLINE mode
3. Run CHECKDB, but do not use the REPAIR option!

You can also execute the following script:

ALTER DATABASE ‘AdventureWorks2019’

SET ONLINE;

DBCC CHECKDB (‘AdventureWorks2019’)
WITH NO_INFOMSGS;

If the command completed without warnings, the database should now be in the ONLINE state, which is the desired outcome.

You don’t need to repair the DB.

Method 2. How to fix RECOVERY PENDING mode – Rebuilding log files in EMERGENCY mode

If the SQL error log shows that the database hasn’t recovered because the log file is missing, deleted, renamed, or corrupted, follow these steps:

1. First, set the database to EMERGENCY mode
2. Detach the database (take it OFFLINE)
3. Re-attach it (bring it ONLINE)

Here is the T-SQL script that does all those steps:

ALTER DATABASE AdventureWorks2019

SET SINGLE_USER;
GO

DBCC CHECKDB (
		AdventureWorks2019
		,REPAIR_REBUILD
		)
WITH NO_INFOMSGS
	,ALL_ERRORMSGS;
GO

ALTER DATABASE [AdventureWorks2019]

SET ONLINE;
GO

ALTER DATABASE [AdventureWorks2019]

SET MULTI_USER;
GO

The system will automatically remove the corrupt logs and will build a new one.

Method 3. How to fix RECOVERY PENDING mode – Start DBCC CHECKDB with REPAIR Option

If the above doesn’t work, the next step is to perform a ‘repair’.

Make sure you have a backup. Don’t do this on the DB which is the ONLY version DB you have, as after repair is complete, you may lose data and you may not like what got lost!

I only recommend the REPAIR option as a last resort. Microsoft recommends restoring from the last known good backup as the primary method for recovering from errors reported by DBCC CHECKDB.

The DBCC CHECKDB with the REPAIR option is a logged and recoverable transaction.

What does that mean to you?

It means if you wrap DBCC CHECKDB with REPAIR into a transaction (execute BEGIN TRANSACTION before running the command) and you don’t like the final result, you can roll the transaction back and get back to where you started.

That way you will be able to ROLLBACK the repair operations if necessary.

There are different repair levels. Start with REPAIR_REBUILD, which performs repairs that have no data loss:

• Set the database to single-user mode
• Run DBBC CHECKDB with REPAIR_REBUILD
• Set the database to ONLINE and re-activate multiple user mode

Here is the T-SQL script below on how to do it:

ALTER DATABASE AdventureWorks2019

SET SINGLE_USER;
GO

DBCC CHECKDB (
		AdventureWorks2019
		,REPAIR_REBUILD
		)
WITH NO_INFOMSGS
	,ALL_ERRORMSGS;
GO

ALTER DATABASE [AdventureWorks2019]

SET ONLINE;
GO

ALTER DATABASE [AdventureWorks2019]

SET MULTI_USER;
GO

If the repair is successful, the database can go online.

If not, DBCC with REPAIR_ALLOW_DATA_LOSS is your next option.

This may result in a loss of data!

Again, make sure that you have backups and you understand the risks involved.

Here are the steps:

• Set the database to EMERGENCY mode
• Run DBBC CHECKDB with REPAIR_ALLOW_DATA_LOSS
• Set the database to ONLINE and set it to multi-user mode

Here is the T-SQL script below:

ALTER DATABASE [DATABASENAME]

SET EMERGENCY;
GO

ALTER DATABASE [DATABASENAME]

SET SINGLE_USER;
GO

DBCC CHECKDB (
		DATABASENAME
		,REPAIR_ALLOW_DATA_LOSS
		)
WITH NO_INFOMSGS
	,ALL_ERRORMSGS;
GO

ALTER DATABASE [DATABASENAME]

SET ONLINE;
GO

ALTER DATABASE [DATABASENAME]

SET MULTI_USER;
GO

References

1. SQL Server Restore and Recovery Overview – Microsoft
2. sys.databases (Transact-SQL) – Microsoft
3. DBCC CHECKDB (Transact-SQL) – Microsoft
4. sp_attach_single_file_db – Microsoft