Microsoft SQL Server database “Recovery Pending” state and 3 ways how to fix it

What is SQL Server Recovery Pending state?

This means the database ran into a problem. SQL Server knows that it needs to run “recovery process” on this db, but there is something preventing this process from running. SQL doesn’t know yet, that recovery will fail or not, and the recovery process simply hasn’t started yet.

Recovery Pending – The good news

The database is likely to be OK. It is less likely it is damaged. So don’t panic yet. Just know that, db will be unavailable to users and will require your action for the error to be fixed before it can be brought back online.

Database in Recovery Pending mode can be a tricky situation and requires DBA knowledge. You may not have the the time to test everything out and execute all the steps properly. We would be happy to help, just reach out using Contact Us form in the top right.

Red9 Team

How to check 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 could be missing
4. Disk drive is full
5. SQL Upgrade didn’t go well or was interrupted
6. Big change was being introduced, and something failed midway, such as changes to FileStream feature
7. LDF (log) file is corrupted
8. MDF (data) file corruption. The MDF file db file can get corrupted for many reasons such as disk or SAN problem, hardware fault, virus, etc.
9. Server restarted, and something changed, such as disk or directory is missing, or permissions
10. Cryptoware encrypted your SQL files

What are the ways of fixing RECOVERY PENDING state?

First, make sure that you have adequate backups of the database in question before starting any repair process.

Start with looking at the SQL Server Error Log.

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

If no, then next, check Windows “Event Viewer”. Same question – do you see anything that stands out?

Method 1 How to Fix Recovery Pending state – Set Database State to ONLINE

When db recovery cannot be started due to insufficient disk storage space, which very coming, do these steps:

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

You can also execute the following script:

1. ALTER DATABASE ‘AdventureWorks2019’ SET ONLINE;
2. DBCC CHECKDB(‘AdventureWorks2019’) WITH NO_INFOMSGS;

If the command has been completed without warning, the database should now be in ONLINE states, and this is what you want. DB doesn’t need to be repaired.

Method 2 how to Fix Recovery Pending state – Rebuilding log files in EMERGENCY mode

In case the SQL error log shows that the database has not recovered successfully due to a database missing log file that may be deleted, renamed, or corrupted:

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 TSQL script that does all those steps:

1. ALTER DATABASE [AdventureWorks2019] SET EMERGENCY;
2. GO
3. ALTER DATABASE [AdventureWorks2019] set MULTI_USER;
4. GO
5. EXEC sp_detach_db ‘[AdventureWorks2019]’;
6. GO
7. EXEC sp_attach_single_file_db @DBName = ‘[AdventureWorks2019]’, @physname = N'<insert file name path here>’;

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

Method 3 how to Fix Recovery Pending state – Initiate DBCC CHECKDB with REPAIR Option

If above doesn’t work, next step is to perform the “repair”.

Make sure you have a backup. Don’t do this on the db that 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 the last resort. Microsoft recommends a to restore from the last known good backup as the primary method to recover 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 end 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. Stat 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 TSQL script below on how to do it:

1. ALTER DATABASE AdventureWorks2019 SET SINGLE_USER;
2. GO
3. DBCC CHECKDB (AdventureWorks2019, REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
4. GO
5. ALTER DATABASE [AdventureWorks2019] SET ONLINE;
6. GO
7. ALTER DATABASE [AdventureWorks2019] SET MULTI_USER;
8. 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 TSQL script below:

1. ALTER DATABASE [DATABASENAME] SET EMERGENCY;
2. GO
3. ALTER DATABASE [DATABASENAME] SET SINGLE_USER;
4. GO
5. DBCC CHECKDB (DATABASENAME,REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
6. GO
7. ALTER DATABASE [DATABASENAME] SET ONLINE;
8. GO
9. ALTER DATABASE [DATABASENAME] SET MULTI_USER;
10. GO

References

1. SQL Server Restore and Recovery Overview. Microsoft. Retrieved Aug 16, 2023
2. “sys.databases (Transact-SQL)“. Microsoft. Retrieved Jun 7th, 2023
3. “DBCC CHECKDB (Transact-SQL)“. Microsoft. Retrieved Jun 7th, 2023
4. sp_attach_single_file_db

Elevate your database servers with our SQL Server managed services, performance tuning, and consulting services, tailored for companies seeking optimal database solutions.