MS SQL database goes into the Recovery Pending state when something prevents the MS SQL Server from starting the database recovery. This might happen due to resource-related issues, missing files, or problem with the database. When the server marks the database in Recovery Pending state, the database is not available for users. In this post, we’ll discuss the possible causes for the Recovery Pending state of MS SQL Server database and the solutions to fix the issue.
Reasons behind Recovery Pending State in SQL Server
SQL Server database may go into the Recovery Pending mode due to one or more of the below reasons:
- The database didn’t shutdown properly.
- Missing or corrupted transaction log files.
- Disk/drive, where the database is saved, is out of storage.
- Database file is corrupted or damaged.
- System is terminated or crashed abnormally.
- Bugs in MS SQL Server.
Methods to Fix Recovery Pending in SQL Server Database Issue
First, you can check the SQL Server error log. This will help you detect any issues that might be responsible for the Recovery Pending state of the database. To check the error logs, open the SSMS, expand the SQL Server Agent, and click Error Logs. If you didn’t find any issues, then follow the below troubleshooting methods to bring the SQL database to normal mode.
1. Turn OFF the AUTO CLOSE Option
Sometimes, the database goes into the Recovery Pending state if the AUTO CLOSE option is ON for the database. When this option is turned ON, SQL Server closes the database after the use and turn it ON next time when a user accesses the database. So, if the database is frequently opened and closed after each connection, it is recommended to turn OFF the AUTO CLOSE option. To do so,
- Open SQL Server Management Studio (SSMS) and connect to the SQL Server Instance.
- Right-click on the database and select Properties.
- On the Database Properties window, click on Options in the left pane.
- Then, set the value as FALSE for AUTO CLOSE and click OK to save the changes.
2. Recreate the Log Files
SQL database can go into Recovery Pending state if the transaction log files are missing or corrupt. You can recreate the log files by detaching and re-attaching the main database. When you detach the database (take the database offline) and then bring it online (re-attach), it rebuild the log files automatically. Here’s how to detach the SQL database:
Note: You can’t detach the database which is published, replicated, suspect, or in a mirroring session.
- Open SQL Server Management Studio (SSMS) and click the New Query option.
- Set the database in EMERGENCY mode by using the below command:
ALTER DATABASE [DBName] SET EMERGENCY;
- Next, change the database to multi_user mode by using the below command:
ALTER DATABASE [DBName] set multi_user
- Now, run the below command to detach the database:
EXEC sp_detach_db ‘[DBName]’
Once the database is detached, run the below command to reattach the SQL database:
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’
3. Repair the Database
SQL Server may fail to start the database recovery if there is corruption in database (MDF/NDF) files. You can repair the database by using the DBCC CHECKDB command. Here’s how to use this command.
First, check the database for any inconsistencies or errors by running the below command with NO_INFOMSGS:
DBCC CHECKDB(database15) with NO_INFOMSGS;
The above command will display the consistency errors (if any) in the database without information messages and also recommends the repair options.
If the DBCC CHECKDB command shows any issues in the database, then you need to repair the database. For this,
Note: Before initiating the repair procedure, make sure to take a backup of the database.
- Set the database in EMERGENCY mode by using the below command:
ALTER DATABASE [DBName] SET EMERGENCY;
GO
- Then, execute the below command to set the database to SINGLE USER mode.
ALTER DATABASE [DBName] set single_user
GO
- Now, run the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option (as given below) to repair the database.
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
- When the repair process is finished, change the database to multi-user mode again by running the below command:
ALTER DATABASE [DBName] set multi_user
GO
An Alternative Solution to Repair SQL Database
Running the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS can result in loss of data. To prevent data loss and to quickly repair the corrupt database, you can use an advanced SQL database repair software, like Stellar Repair for MS SQL. It is a specialized software that can repair corrupt database and help bring the database back online from the Recovery Pending state. It can recover all the data from corrupted NDF/MDF files with complete integrity and precision.
Key Features of Stellar Repair for MS SQL
- Repairs corrupt and inaccessible MS SQL database (.mdf and .ndf) files.
- Recover all the database components, like tables, keys, indexes, triggers, rules, stored procedures, etc.
- Recover deleted records.
- Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008, 2008 R2, and lower versions.
Steps to Use Stellar Repair for MS SQL Software
- Download, install, and run Stellar Repair for MS SQL software.
- From the Select Database window, click Browse or Search to select the database file you want to repair.
- You will see two options – Standard Scan and Advanced Scan to scan the corrupt database. Select the Standard Scan mode. If the database file is highly corrupted, then click on Advanced Scan. Click OK.
- Click Repair to start the repair process.
- Once the repair process is complete, the software will show all the recoverable items in the database.
- To save the repaired file and its components, click Save on the File menu.
- On the Save Database window, do the following:
- Choose MDF under Save As.
- Select New Database or Live Database under Saving Option.
- Enter the required details in the Connect To section.
- Click Browse to choose the destination for saving the repaired file.
- Click Save.
- When the ‘Save Complete’ message box appears, click OK.
The repaired file will be saved at the selected location.
Conclusion
SQL database can go into Recovery Pending state due to several reasons. In this post, we’ve explained how to fix the Recovery Pending state and bring the database back online. If the Recovery Pending mode of the database is due to corruption, then you can use Stellar Repair for MS SQL software to repair the database. It can repair the database (MDF/NDF) file and restore all the objects with complete integrity. It can also help you fix several corruption-related errors in SQL database.
Was this article helpful?
