[Fixed] Database in Recovery Mode SQL Server 2014

Summary

This post discusses the instances that put a SQL Server 2014 database in recovery mode and what causes the database to get stuck in recovery. Also, it discusses the methods to restore the database. 

You may find a SQL Server 2014 database in recovery mode when you restart the SQL Server service or restore a database from SQL Server Management Studio (SSMS). Also, a database goes into recovery mode if you restart a system abnormally. The database goes into recovery in such a scenario until all the active transactions are committed or rolled back. Let the recovery process complete, and the database comes back online.

However, the database gets stuck in the recovery mode at times, increasing downtime. This may happen due to any of these reasons:

• Huge uncommitted transactions: The recovery process may slow down if you have huge uncommitted transactions that you need to record in the transaction log file. 
• Lack of space: Some space is reserved in a log file by SQL Server to record the rollback transaction. In some situations, the rollback fails when there is no more unused space.
• Too many virtual log files (VLFs): A transaction log file comprises smaller files (i.e., virtual log files). All the log records are written in VLFs. Lots of virtual log files can cause the database recovery to slow down.   

If you need to restore your database, you can use a few methods to bring the database online.

How to Restore SQL Server 2014 Database in Recovery Mode?

Note: Avoid bringing the database online using the RESTORE ...WITH RECOVERY command, running it will make the database go through the recovery process again to get the database to a consistent state.

Following are the two methods you can use to restore the database to its normal state:

Method 1 – Remove MDF/LDF Files and Delete the Database

Note: Do not try the below steps on a production database. 

You can try to delete the database and restore it from an updated backup. However, you may fail to delete the database stuck in recovery mode. To perform the restore process, you will first need to find and remove MDF and LDF files and then delete the database. Before removing the database files, create a backup of those files. After creating the backup, follow these steps:

• Launch SSMS and connect to an instance of SQL Server 2014. 
• From the Object Explorer pane, expand the Databases node, and then right-click on the database you want to delete. Select Properties.
• From the ‘Database Properties’ window, click on the Files option under Select a page from the left pane. 
• In the screen that appears, select the MDF and LDF files, and then click on the Remove button.

After removing the SQL database files, try deleting the database and then restore the original DB from backup.

Method 2 – Use SQL Recovery Tool

If nothing works, using a SQL database recovery tool can help restore the database to its original form with minimal or no downtime. It helps repair the database MDF/NDF file and recover all of the file contents. You can use the repaired file to restore the database. However, choosing the right SQL recovery tool is crucial to regain access to the database and its data.

Conclusion

A database may go into recovery mode on restarting your SQL Server after a crash or trying to restore the database. Some of you may find that the recovery process is taking longer to complete or is stuck. This may happen if there are huge transactions that need to be committed or rolled back. Also, too many virtual log files or a lack of space to record the uncommitted transactions can also delay the recovery. If you need to restore the database urgently, you can delete the database and restore it from an updated backup copy. If this doesn’t work, using Stellar Repair for MS SQL can help you restore the database to its original, intact state.