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 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."
.jpg)
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
If you’re still unable to fix Recovery Pending state in SQL server, contact a professional SQL database repair service provider,
such as Stellar®. The experts at Stellar® can fix all the SQL database files -- primary (MDF), secondary (NDF), and transaction log (LDF) -- which might be causing the issue.
The seasoned professionals at Stellar® use indigenous tools and technology to fix SQL database issues. They can help you fix any type of corruption in database files such as corruption of file header, tables, page level corruption, data pages, metadata, etc.
They ensure that you get all your SQL data in its original form and integrity. In addition, these experts can repair the database even if the SQL database storage disk is physically or logically damaged.
About The Author
Sr. Online Marketing Executive and Content Writer at Stellar Data Recovery.
