,
The “Recovery Pending” status on a SQL Server database indicates that the database needs to be recovered but the recovery process cannot begin because either there is not enough free disk space or the database files are missing or damaged. The issue usually happens after an improper shutdown of the SQL Server or if the SQL Server service crashes for any reason.
How to fix the “Recovery Pending” status on a SQL Server database.
Step 1. Verify that there is sufficient free disk space.
The “Recovery Pending” error on a SQL database can occur when the disk space is full or when the database is corrupted. So, ensure first that the disk containing the database files has sufficient free space* and then continue to next step.
* Note: If there is not enough free space on the disk containing the “Recovery Pending” database, then delete or move some files to another disk, and then restart SQL Server in Microsoft SQL Server Management Studio (SSMS).
Step 2. Verify Database Existence.
Go ahead and make sure the database files are there. *
* Note: In a typical SQL Server installation the database files are located in the following path:
- C:Program FilesMicrosoft SQL ServerMSSQLxx.ERASQLINSTANCEIDMSSQLDATA
Step 3. Check Database File Permissions.
Verify that SQL Server has the necessary permissions to access the database files. To do that:
1. Right-click on the database files and select Properties.
2. In Security tab ensure that the MSSQL account has Full control permissions. If the SQL Service account is missing click Edit and then Add it with full permissions.
Step 4. Restart SQL Server Service.
One of the most common reasons for a “Recovery Pending” status on a SQL database is that the SQL Server service is not running. So, do the following:
1. Navigate to Windows services (services.msc).
2. Locate the SQL Server service* and if is not running, right-click on it and choose Start. Otherwise, if the service is already running, right-click and restart it.
* Note: If you’re using SQL Express, restart the SQL Express Service.
3. Now wait 4-5 minutes, and then see in Microsoft SQL Server Management Studio if the problem if solved. If not, continue to next step.
Step 5. Restart MS SQL Server in SSMS.
1. In Microsoft SQL Server Management Studio (SSMS), right-click on the SQL Server and select Restart.
2. Wait 3-4 minutes and then expand the Databases section and check if the “Recovery Pending” error persists. If so, proceed to next step.
Step 6. Detach and Re-attach the Database.
The next step to fix the “Recovery pending state”, is to detach and re-attach the problematic database. To do that:
1. In Microsoft SQL Server Management Studio (SSMS), right-click on the “Recovery Rending” database and select Tasks > Take Offline. Then, click OK in the ‘Take Database Offline’ window
2. Then, right-click again on the database and select Tasks > Detach. click OK in the ‘Detach Database Offline’ window.
3. Now right-click on Databases and select Attach.
4. In the Attach Databases window, click Add.
5. In ‘Locate Database Files’ window, select the database and click OK and then OK again to re-attach the database. *
* Note: If you cannot re-attach the database with error “Cannot detach a suspect or recovery pending database. It must be repaired or dropped“, continue to next step.
6. Finally, restart the SQL Server, wait 4-5 minutes and then expand “Databases” to find out if the “Recovery Pending” error is solved. If not, continue to next step.
Step 7. Repair SQL “Recovery Pending” Database.
If after applying the above steps, you still haven’t solved the problem, proceed to repair the database in Microsoft SQL Server Management Studio using the below steps:
1. In SSMS, stop the SQL Server.
2. Then make a copy of the “Recovery Pending” database files (.MDF & .LDF) to another location (for backup reasons).
3. Now, start again the SQL Server in SSMS and then execute the below commands/queries to repair the database:
4. Set the Database in Emergency mode with the command/query below:*
- ALTER DATABASE DatabaseName SET EMERGENCY;
- GO
* Info: This command will set the database to read-only mode, allowing you to perform further troubleshooting.
5. Run Database Consistency Checks:*
- DBCC CHECKDB (DatabaseName);
- GO
* Info: This command will check the physical and logical integrity of all the objects in the database.
6. Set Database to Single User Mode:*
- ALTER DATABASE DatabaseName set single_user
- GO
* Info: This command will set the database to single user mode, allowing you to perform repair operations.
7. Repair the Database:
- DBCC CHECKDB (DatabaseName , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
- GO
* Info: This command will repair the database with possible data loss, so use it if you have no other choice.
8. Set Database Back to Multi-User Mode:
- ALTER DATABASE DatabaseName set multi_user
- GO
9. After executing the above commands, see if the database comes online. If not, try to detach and re-attach the database using the instructions on step-6 above.
ADDITIONAL HELP.
If, after following the steps above, you are still experiencing the “Recovery Pending” error on your database, then you can try to fix the problem, either by restoring the affected database from a previous backup or by examining the SQL logs Server.
* Note: To view the SQL Server Logs, expand Management in SSMS and then click SQL Server Logs > Current.
That’s it! What worked for you?
Let me know if this guide has helped you by leaving your comment about your experience. Please like and share this guide to help others.
