In SQL Server Management Studio (SSMS), you can see the SQL Server database state. Alternatively, you can use T-SQL commands to verify your database status. You can use the DATABASEPROPERTYEX function to check the database status. The following example will show the database name and the database status. If there is a problem, the database status will show as Recovery Pending. In the below example, the database name is ‘stellar’.
SELECT DB_NAME() AS DatabaseName,
DATABASEPROPERTYEX('stellar', 'Status') AS DatabaseStatus
Another way to verify the database status is by using the sys.databases system view.
SELECT name, state_desc
FROM sys.databases
WHERE name ='stellar'
Here, ‘state_desc’ is the database status or state and ‘stellar’ is the database name. In this query, we are checking the database state of the stellar DB.
Possible Database States
There are different database states:
- 0 means that the database is ONLINE.
- 1 is the restoring status.
- 2 is the recovering status.
- 3 is the recovery pending status.
- 4 is the suspect status.
- 5 is the emergency status.
- 6 is the OFFLINE status.
- 7 is the copying status.
- 10 is the OFFLINE_SECONDARY status.
In this article, we will cover the state 3, i.e., recovery pending status. The recovery pending status for a long time may mean that your database is damaged. The following are some common reasons:
- Hard drive is damaged. If the hard drives are used constantly, they can get damaged over time. A sudden power failure can also damage the hard drive.
- Virus or malware attacks. Such attacks can damage the hard drive.
- A power failure may also damage your hardware and your database as well. If the server is shut down or the service is unexpectedly stopped, the transaction log can fail and produce the Pending Recovery error.
- If there is insufficient disk space, you may have problems recovering your data. If there is no disk space, the database files will be locked and it will not be possible to restore the data.
- If too many resources are used in memory, there won’t be enough memory to restore the database. If there is a lack of memory, the recovery pending error may occur if you try to restore the database.
- Natural disasters, like earthquakes, storms, floods, volcanic activity, extreme temperatures, landslides, and wildfires may damage your hardware and then your database log files can be damaged.
What to do to avoid these problems?
To protect your database and avoid the Recovery Pending error, you can take the following actions:
- First, save your backups in a different location. Make sure that your SQL Server backups are stored in a different location than your server because a natural disaster could damage not only your server but also all the servers in your company. It is a good idea to store your backups on the cloud. You can use cloud services, like Azure, AWS, Google Cloud, etc.
- Second, use the SQL Server Always On recovery solution, if possible. This solution maintains not only the SQL databases but also the entire instance.
- Third, use disk mirroring with hardware or software solutions to avoid problems. If you have virtual machines, there are several solutions to restore your server using snapshots.
- Finally, make sure to have the latest Windows and SQL Server updates to keep your environment secure. Also, teach your staff about security to avoid phishing emails, malicious attachments, or websites with software that installs malware.
How to fix damaged databases?
The Recovery Pending status usually means that the SQL Server transaction file is damaged. If that is the case, you need to repair the database. To do that, you can use Stellar Repair for MS SQL. This software is used to repair databases.
To use it, make sure that your database is offline.
Once the database is offline, create a copy. You can work with the copy and not with the original file.
Now, launch the software, select the database file, and then press the Repair button to repair your database.
Once repaired, you can save your data. You can save it in a new database or the current Live Database.
You can also save the data in other formats, like Excel, CSV, and HTML.
Conclusion
In this article, we learned about the SQL Server Recovery Pending error, how to check it, and how to avoid this issue. If the error occurred due to damaged or corrupted database files, then you can use Stellar Repair for MS SQL to repair the database (MDF/NDF) files and restore all the objects. The software is compatible with MS SQL 2019, 2017, 2016, and earlier versions.
