Recovery Pending in the SQL Server Database - Causes and Solution

Suppose while working in SQL Server, a database contained in the availability group transitions to a Recovery Pending state. If this happens on the primary replica of the group, it will leave your database inaccessible via the client application. You cannot remove or drop the database from the group. Eventually, you will receive a message regarding recovery pending in the SQL server database.

While manual methods to repair an SQL server database are easy to follow, automated methods are even more convenient and successful in setting the database free from the pending recovery state. Stellar Repair for MS SQL is among the best SQL recovery tools to take care of this situation.

In this blog, we will talk about this issue in detail along with the underlying reasons and solutions to resolve it.

How to know if your database is in a recovery pending state?

To find out if your database is working fine or is in a recovery pending state, run the following script. It will give you the existing state of the database.

SELECT
    dc.database_name, d.synchronization_health_desc,
    d.synchronization_state_desc, d.database_state_desc
FROM sys.dm_hadr_database_replica_states d
    JOIN sys.availability_databases_cluster dc ON d.group_database_id = dc.group_database_id
AND d.is_local = 1

If you find the database status: NOT_HEALTHY and in RECOVERY PENDING or SUSPECT state, there is something wrong with it. Unless you recover the database, it will remain inaccessible for use.

Alternatively, you may run the below SQL query to find the database state:

SELECT name, state_desc from sys.databases
GO

This will list the existing database followed by their states.

If any of these databases display Recovery Pending under the state_desc column, it indicates that your DB is in the pending stage.

Here is a brief description of all the database states you will get after running the above query.

• Online: The SQL Server remains online even if there is a single damaged file
• Suspect: If you fail to recover the damaged data, you will see the SUSPECT state
• Recovery Pending: When the server is unable to start database recovery, it displays a RECOVERY PENDING state

Reasons behind recovery pending state

The recovery pending state occurs when the Database engine is unable to start the recovery process for a selected database. This makes the database inaccessible until the resolution of the recovery pending state. Many reasons account for this situation:

• Improper database shutdown: It leaves at least one under-progress transaction uncommitted. The abrupt shutdown also removes its log file, making its recovery a difficult task.
• Inadequate RAM: Database recovery may suffer a blow because of insufficient disk storage or RAM.
• Relocation of the log files: Often, the users transfer the log files to a different drive to address concerns related to server performance. This move can backfire, as it can corrupt the log file.
• Crashed SQL Service: A crashed out or an abruptly terminated SQL service may also leave the database in the recovery pending state.
• Other reasons: Some other factors responsible for this state may include corrupted or missing MDF file or log file, unsuccessful SQL upgrade, encrypted SQL file due to the use of a cryptoware, etc.

Fixing the recovery pending in the SQL server database issue

Before proceeding with the recovery methods, it is advisable to take a backup of your database. In case of some mishap, it will ensure that you have a backup copy. Now let’s discuss the methods to resolve this issue of Recovery pending in SQL server database.

1. Set the state of the SQL Server database to ONLINE

As we have already discussed, insufficient disk storage is one of the foremost reasons behind the corrupted database. You can seek the help of MS SQL to repair database in this situation.

Increase the size of the database in the SQL Server Management Studio. For this, you can execute the below command:

ALTER DATABASE SQL_TST_DB
MODIFY FILE
    (NAME = SQL_TST_DB,
    SIZE = 128MB);

Here, we have increased the size to 128 MB, you can increase it to a limit as per your convenience.

After increasing the file size, the next step would be to set the database to ONLINE mode. Next, run the CHECKDB command without using the REPAIR option.

ALTER DATABASE SQL_TST_DB SET ONLINE;
DBCC CHECKDB (SQL_TST_DB) WITH NO_INFOMSGS;

Here, we have increased the size to 128 MB, you can increase it to a limit as per your convenience.

After increasing the file size, the next step would be to set the database to ONLINE mode. Next, run the CHECKDB command without using the REPAIR option.

ALTER DATABASE SQL_TST_DB SET ONLINE;
    DBCC CHECKDB (SQL_TST_DB) WITH NO_INFOMSGS;

If the command executes without warning, the database is set to ONLINE state. You do not need to repair it anymore.

2. Initiate forceful repair using C

As you already know, EMERGENCY mode sets the database to READ_ONLY state and allows access to only system admins.

• When using MS SQL to repair database, use this command to set the DB in EMERGENCY mode
  

  ALTER DATABASE [SQL_TST_DB] SET EMERGENCY;

• After that, set the DB in the Single_User mode.
  

  ALTER DATABASE [SQL_TST_DB] set single_user

• Next, try to repair the SQL server database with the help of the DBCC CHECKDB command.
  

  DBCC CHECKDB ([SQL_TST_DB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

• After the repair, set the database back to multi-user mode.
  

  ALTER DATABASE [SQL_TST_DB] set multi_user  

This will repair your database and set it free from the recovery pending state.

3. Using emergency mode to rebuild log files by database detach and re-attach

The SQL error log may display a missing log file as the reason behind the Recovery pending in SQL server database. Here is how to fix it.

• Set the database to emergency mode
• Bring the database OFFLINE (Detach it)
• Bring the database back to ONLINE (Re-attach it)

Here are the commands for the same:

ALTER DATABASE SQL_TST_DB SET EMERGENCY;
ALTER DATABASE SQL_TST_DB SET MULTI_USER
    EXEC sp_detach_db ‘[SQL_TST_DB]’
    EXEC sp_attach_single_file_db @DBName = ‘[SQL_TEST_DB]’, @physname = N'[mdf path]’

4. Using SQL Repair tool

If none of the above methods work, using recovery software will always remain a reliable option. The use of Stellar Repair for MS SQL is widespread in this context.

A popular tool among individuals and big organizations, Stellar Repair for MS SQL works actively in repairing the corrupt database. It brings back your DB online from the Recovery Pending State without hassles.

Here are the steps to solve the issue of recovery pending in SQL server database with the help of Stellar Repair for MS SQL.

1. Install and Launch the software
2. Provide the location of the MDF file that is creating problems in opening and displaying the recovery pending message.
3. Click Repair to start repairing the corrupted file.
4. Choose the Scan Mode, whether Standard or Advanced. The standard scan will be advisable. But depending on your requirement, you may proceed with the Advanced scan as well.

After the successful completion of the repair, you will get the message: Selected MS SQL database repaired successfully.

Key features of Stellar Repair for MS SQL

You can download this software free. Here are some of its main features:

• Recovers the damaged and inaccessible NDF and MDF files.
• Restores deleted data
• Recovers tables, keys, stored procedures, rules, triggers, and other database components
• Has gained the trust of Microsoft MVPs
• Supports all the MS SQL versions to repair database from 2008 to 2019

Final Words

While accessing the SQL server database, you may encounter a Recovery Pending message. It indicates the uncommitted transaction due to an abrupt shutdown or space shortage in the disk or because of some other reason. In such a case, you may try several manual methods with the help of MS SQL to repair database.

Alternatively, the use of Stellar Repair for MS SQL is also widespread among the users. Depending on ease of use and your budget, you can choose from among various versions of this popular software. To start with, you may proceed with the free edition of the Stellar Repair for MS SQL as well.