How to Fix Recovery Pending State in SQL Server Database?

Microsoft’s SQL server is a relational database management system. Businesses use SQL server to store critical data. SQL server performs various I/O operations to read the disk and transfer data when you carry out an activity. During these operations if the server finds any inconsistency in data or a problem with I/O system, it tries to recover database. In its recovery attempt if the server fails to recover the database, it marks the database in Recovery Pending state.

Free Download 100% Secure

When the administrator tries to bring the server online, the server may throw the below error.

"Error: 5173, Severity: 16, State: 1. One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from backup."

Reasons behind the Recovery Pending State Issue

There are various reasons that may result in Recovery Pending state in SQL server. Some of them are:

• The database is corrupt
• The storage disk’s file system is damaged due to which the server is unable to perform I/O operations properly
• There are hardware problems with the database storage drive, resulting in inconsistency in data
• The transaction log files are deleted or corrupt
• Server cannot recover the database because of insufficient memory space or disk storage

Quick Fixes for Recovery Pending State

Here are some quick fixes that can help you identify the cause and fix the issue.

• Check memory and disk space on your SQL server
• Run hardware diagnostics: You can run hardware diagnostics to check if there is any problem with the server hardware or the storage drive, which may be causing the Recovery Pending state issue. Also, you can check the application logs and the SQL server error logs to find any fault in the hardware. In case there are any hardware problems related to the server, you can fix them by replacing the faulty component and resolve the recovery pending issue.
  [NOTE]: If the cause of the issue is physical or logical failure of the storage drive, do not try any method to bring the server online. Doing so might cause further damages and result in permanent data loss. In such a case, seek the help of a professional data recovery service provider to rescue your SQL server data.
• Restore from good backup: If you have the backup of database and transaction logs, you can restore the data from backup. This may help you get rid of the Recovery Pending state issue if it has occurred due to corrupt and inconsistent data.

However, if the backup is not available, follow the below given methods:

Methods to Fix Recovery Pending State in SQL Server

If your SQL server is in Recovery Pending state due to corruption of database or transaction logs, follow the methods mentioned below to fix the problem.

Method 1. Detach and Re-attach the Main Database

To detach and reattach the database, you first need to set the database in EMERGENCY mode. Once the database is in EMERGENCY mode, you can bring the database offline (detach), and then bring it back online (attach). To do so, execute the following set of queries in SQL Server Management Studio (SSMS):

Step 1. Change the database status to EMERGENCY:
ALTER DATABASE [dbname] SET EMERGENCY

Step 2. Put the database in multi-user mode:
ALTER DATABASE [dbname] SET MULTI_USER

Step 3. Detach the database:
EXEC sp_detach_db '[dbname]'

Step 4. Reattach the data file only (do not attach log file):
EXEC sp_attach_single_file_db @dbname = '[dbname]',
@physname = N'[mdf path]'

The above steps build new log file automatically, and get rid of the corrupt one due to which you may be facing the issue with your SQL server.

Method 2. Use DBCC CHECKDB

You can use Microsoft SQL server’s DBCC CHECKDB command-line utility to fix minor corruption in database. You can use this utility with any of the below given arguments to fix the database or any data inconsistency issue that may be causing SQL server to enter into Recovery Pending state:

• REPAIR_FAST
• REPAIR_REBUILD
• REPAIR_ALLOW_DATA_LOSS

[Before you begin]:

• Make sure that you set the database to EMERGENCY mode and SINGLE_USER mode before running DBCC CHECKDB with any argument
  Command to set database to SINGLE_USER mode:
  ALTER DATABASE [dbname] SET SINGLE_USER
  Command to set database to EMERGENCY mode:
  ALTER DATABASE [dbname] SET EMERGENCY
• Change the database mode to MULTI_USER after the execution of DBCC CHECKDB command is over
  Command to set database to MULTI_USER mode:
  ALTER DATABASE [dbname] SET MULTI_USER
• Back up your database before running the DBCC CHECKDB command

1. Use REPAIR_FAST Argument

The database backward compatibility issues can be fixed by using REPAIR_FAST argument with DBCC CHECKDB command. You can run the following query:

DBCC CHECKDB (N ‘dbname’, REPAIR_FAST) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

2. Run DBCC CHECKDB with REPAIR_REBUILD Argument

When DBCC CHECKDB runs with REPAIR_REBUILD argument, it rebuilds index and repairs missing rows in non-clustered indexes, etc.

Here’s an example of the repair query:

DBCC CHECKDB (N ‘dbname’, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

3. Use REPAIR_ALLOW_DATA_LOSS Argument

[CAUTION]: Using REPAIR_ALLOW_DATA_LOSS argument to repair MS SQL database might lead to data loss.

Using REPAIR_ALLOW_DATA_LOSS argument with DBCC CHECKDB command allows you to repair all reported errors or corruption in SQL database files. However, during the repair process, there might be deallocation of a row, page, or series of pages, resulting in data loss.

The command with REPAIR_ALLOW_DATA_LOSS argument is given below:

DBCC CHECKDB (N’dbname’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO