Several MS SQL users have reported encountering issues while restoring or accessing the database. One of the user cases is below:
“My server is shutting down because of the electrical. And when I opened my database in SSMS, database is recovery pending. I checked my ERROR LOG, the message are :
4 transactions rolled forward in database ‘POSDW’ (14:0). This is an informational message only. No user action is required. restoreHkDatabase: DbId 14, Msg 41313, Level 16, State 1, The C compiler encountered a failure. The exit code was 2.
[ERROR] Database ID: [14] ‘POSDW’. Failed to load XTP checkpoint. Error code: 0x82000009. (d:\sql12_main_t\sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostdb.cpp:3126’RecoverHkDatabase’) Error: 41313, Severity: 16, State: 1.”
The recovery pending state indicates that the SQL Server fails to restart recovery on the database due to any reason. It can occur due to multiple reasons. In this post, we’ll learn the causes behind the Recovery Pending state in SQL Server and their solutions. Your database becomes inaccessible during the recovery pending state. Your Server marks the database in this state due to one of the following reasons:
- The database didn’t shut down properly.
- Missing, damaged, or corrupted transaction log files.
- The database file is corrupted or damaged.
- Bugs in MS SQL Server.
- Resource-related issues on the system hosting SQL database. E.g., disk is out of storage.
- The system is terminated or crashed abnormally.
How to check the Recovery Pending state in SQL Server?
You can find the current state of all the SQL Server databases by using the “state_desc” column in the “sys.databases” catalog view:
SELECT name, state_desc FROM sys.databases;
You can also check this status on the database in SSMS
Methods to fix Recovery pending state issues in SQL:
When you experience this issue, first check your server error log to understand the exact cause. Here are the steps to view the error log:
- In SSMS, click Object Explorer.
- In Object Explorer, connect to an instance of SQL Server.
- Find and expand the Management section, then right-click “SQL Server Logs” option.
- Select View and click SQL Server Log.
- In the Log File View window, you will see a list of logs. Check and find the log regarding the recovery pending state.
Next , follow the below solutions to recover the SQL database from recovery pending state.
Solution 1- Check the Disk space:
The state of the SQL server database can change to Recovery Pending due to insufficient disk space on your system. If your backup recovery fails with Recovery pending status, then you can check the space on your system using the following steps:
- In your system’s search field, type Storage settings and then press Enter key.
- In the Settings window, under System, then click Storage .
If you found the disk is full, then try to free up space by deleting the unused files. Also try moving file to another disk to fix the disk space issue.
Solution 2-Reset the Log files
The SQL Server database can result in Recovery pending state if there is some issue with the transaction log files. In such a case, you recreate the log files. To do this, detach and re-attach your main database. Performing detaching and then re-attaching the database will automatically rebuild the log files. Here’s how to do so:
- In your SSMS, click the New Query option.
- Run the below command to detach the database
EXEC sp_detach_db ‘[DBName]’
- Next, re-attach the SQL database:
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’
Solution 3. Repair the Database
The database can move into the Recovery pending state when there is some problem with the SQL database file or your database is damaged/corrupted. To repair the database file, you can use the DBCC CHECKDB command. Running this command with the repair option will remove corruption in indexes and other data in the database. Here are the steps to repair the SQL database using the DBCC CHECKDB command:
Before executing this command, first run the DBCC CHECKDB without Repair command to check if there is corruption in the database. Here is the command:
DBCC CHECKDB ‘database_name’;
If the command shows corruption, then follow the below steps to repair the database:
- First, change the database state to EMERGENCY mode so that the database becomes accessible. Run the below command:
ALTER Database DBLogTest SET EMERGENCY;
- Next, change the database mode to SINGLE_USER mode by using the below steps:
- In your SSMS’s Object Explorer, connect to an instance of the SQL Server Database engine and expand it.
- Right-click on the affected database that you want to change and then click Properties.
- In the Database Properties window, click Options.
- In the Options page, click Single from Restrict Access.
- Next, run the DBCC CHECKDB command with the REPAIR option:
DBCC CHECKDB (‘Database15’, REPAIR_ALLOW_DATA_LOSS);
Solution 4-Use a Professional SQL Repair tool:
Using the DBBCC CHECKDB command, does not guarantee the complete recovery of the database. It can help you access the database but with some data loss. The perfect and best alternative to repair MSSQL database without data loss is to opt for a reliable SQL repair tool like Stellar Repair for MS SQL. It is an excellent recovery tool to repair SQL database at any condition. It can even help you recover all data from the database with complete integrity.
Conclusion:
Above, you have learned how to recover an inaccessible database due to the Recovery pending state. You can follow the methods described above to bring back the database from the Recovery pending state to the normal state. If corruption is behind this issue, then you can try a professional SQL recovery tool like Stellar Repair for SQL to restore your database without minimum downtime. It can repair and restore databases to help you bring them online without any data loss. The tool supports repairing both MDF and NDF files without any file size limitations. It also allows you to preview the recovered data before saving. It will help you verify how accurately the tool works. The tool supports all versions of SQL Server.
