A database stuck in recovery mode may stall the working of MS SQL Server. Finding the reason behind this state will lead to a prompt and precise resolution of the problem. Several troubleshooting methods can help fix this issue. If these solutions fail to deliver the expected results, you can use professional SQL recovery software, such as Stellar Repair for MS SQL. Let’s discuss all these points in detail in this blog.
SQL Server Database and Recovery Mode
An SQL Server database goes to (In Recovery) mode mostly when you restart SQL Server or restore the database from a recent backup. Entering this mode enables the database to roll back the incomplete transactions, helping it to:
- Maintain data integrity
- Prevent data corruption, and
- Ensure transactional consistency
After the completion of the recovery process, the database will come online automatically. In adverse cases, the database remains halted in the recovery state, causing slow recovery. Here, it is crucial to find out why the database remained stuck in Recovery mode.
What Causes SQL Server Database Stuck in Recovery Mode?
Checking the SQL Server error log can help to find out the reason behind the issue of the SQL Server database stuck in Recovery mode. For instance, while restoring the database from full, differential, and log backups, you have enabled the NORECOVERY option. However, there is no specific RECOVERY during the final restore.
Here are more reasons that may compel the database to remain stuck in Recovery Mode
- Rolling back a long-running transaction
- Large size of the transaction log file
- The log storage is completely occupied, causing SQL Server to stop all the running transactions
- Excess virtual log files (VLFs) in the DB transaction log are slowing down the recovery process
- An SQL server bug is preventing the database from recovering from the pending state
Resolving the ‘SQL Server Database in Recovery Mode’ Issue
Several methods can prove effective in troubleshooting this SQL Server database problem. But before trying any of them, it would be better to wait and let the process of database recovery complete. This may take hours and often days, but waiting from 2-3 hours to 6-8 hours is a better option depending on the size of the database, whether medium or large. If you cannot wait that long, you may try any of these fixes.
- Turn the AUTO CLOSE Option OFF
If the AUTO CLOSE option for the database is ON, it signals the SQL Server to close the database. The next time the user accesses the database, the server opens it again. This repeated opening and closing of the database after every connection could compel the database to remain stuck in the Recovery Mode. Therefore, it is better to switch OFF the AUTO CLOSE option. Here are the steps for the same.
- Launch SQL Server Management Studio (SSMS)
- Connect to your SQL Server Instance
- In the left panel, right-click the database
- Select Properties
- In the resulting Properties window, click Options on the left side
- Look for AUTO CLOSE, and set its value to FALSE
- Save the changes by clicking OK
Recreate the log files
Missing or corrupt transaction log files can be the reason behind the database stuck in the Recovery mode. An alternative solution will be to rebuild the log files, which will happen automatically by taking the database offline (detach) and then bringing it back online (re-attach).
Here, it is crucial to note that you cannot detach a published, suspected, replicated, or mirrored database. Let’s go through the complete procedure:
- Open SQL Server Management Studio (SSMS)
- Connect to your SQL Server Instance
- From the main menu, click New Query to open the window
- Use the command below to set the concerned database in Emergency mode. Here, we will consider SQLTestDB as the name of the stuck database.
ALTER DATABASE [SQLTestDB] Set Emergency;
- Reset the database mode to multi_user through the following command:
ALTER DATABASE [SQLTestDB] Set Multi_user
- Detach the database to send it offline by typing the command as given here:
EXEC sp_detach_db ‘[SQLTestDB]’
- Next, reattach the database to bring it online. Here is the command:
EXEC sp_attach_single_file_db
@DBName = ‘SQLTestDB’,
@physname = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\SQLTestDB.mdf’;
This will resolve the situation. If the problem persists, consider following other methods.
- Repair the Database
In case of a corrupt or damaged SQL Server database, the recovery process is likely to fail. In such a situation, you may proceed to repair the database MDF/NDF file with the help of the DBCC CHECKDB command. Before proceeding with the repair steps, make sure to have a recent healthy backup of the database. Here are the requisite steps.
- Check the database for errors or inconsistencies by using the NO_INFOMSGS option
DBCC CHECKDB(SQLTestDB) with NO_INFOMSGS;
This command will show the consistency errors in the database without any information message, with suggestions on repair options.
- Use the command below to set the database in Emergency mode. We will again consider the same SQLTestDB as the name of the stuck database.
ALTER DATABASE [SQLTestDB] Set Emergency;
- Next, change the database mode to Single_User
ALTER DATABASE [SQLTestDB] set Single_User
- Run the REPAIR_ALLOW_DATA_LOSS option to repair the corrupt database stuck in Recovery mode
DBCC CHECKDB ([SQLTestDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
Note: Use this command only if no other options work for you. You must have a backup of the database before proceeding with this step.
- After the completion of the repair process, switch the database back to multi-user mode as shown in the command below:
ALTER DATABASE [SQLTestDB] Set Multi_User
This will repair your database and set it free from Recovery Mode. However, if the problem still exists or if REPAIR_ALLOW_DATA_LOSS runs the risk of deleting a part of your database, an alternative solution will be to use professional SQL recovery software.
Use Stellar Repair for MS SQL
The professional SQL recovery software can fix your SQL Server database stuck in Recovery mode quickly and with ease. Here are some of the prominent functions it performs:
- Repairs corrupt or inaccessible MDF and NDF database files
- Recovers the entire database components, including tables, keys, triggers, etc.
- Recovers deleted records
- Lists a preview of recoverable objects
- Resets the database file password
- Performs selective recovery of SQL objects
- Supports MS SQL 2019, 2017, 2016, and lower versions
Additionally, you may also use Stellar Repair for MS SQL to fix several errors, such as:
- ERROR 823/824/825: I/O consistency-based SQL errors
- Can’t Reattach Database: Unable to reattach a database
- ERROR 8946: Causing data corruption
Stellar Repair for MS SQL Software: Steps to fix the database
The professional SQL recovery software can resolve the issue of a database stuck in recovery mode in a few simple steps:
- Download, install, and run the Stellar Repair for MS SQL software
- In the Select Database screen, click Search or Browse to choose the database you wish to repair
- Next, you will see Standard Scan and Advanced Scan. For a highly corrupted database file, select Advanced Scan. Otherwise, choose Standard Scan mode and click OK
- To start the process, click Repair
- After the completion of the repair process, you will preview all the recoverable database items
- From the File menu, click Save to save the repaired file and its components
- Next, a Save Database window will appear with multiple options:
- Under Save As, choose MDF
- Under Saving Option, select New Database
- Under Connect To, provide the requisite credentials
- Next, click Browse to save the repaired file in a preferred location
- Click Save
- As you receive the Repair Complete message, click OK.
Following these easy steps will fix the recovery mode issue of your database and save the repaired file in the provided location.
To buy the software, visit the official website of Stellar Info. If you wish to scan & preview recoverable SQL server database objects, downloading the free trial version of the professional SQL recovery software would be enough.
Conclusion
A halted recovery state makes the database inaccessible, corrupt, and damaged. Fixing this state is possible through multiple ways, with the help of T-SQL queries or through the SSMS interface.
At times, these methods may not work as expected, take more than the usual time, or cause loss of data. In any such instance, using automated and professional SQL recovery software would be advisable. Notably, Stellar Repair for MS SQL is perfect in this context.
The reputable data recovery application holds widespread acceptance among small and large corporate houses. It is easy to use and fixes multiple issues related to SQL Server database, including the ‘database stuck in recovery’ mode problem.
