Whenever the MS SQL Server restarts, it runs recovery of each database in that instance to ensure all the databases are in a consistent state. The recovery process consists of three phases: the Analysis phase, the Redo phase, and the Undo phase. This process takes place in the background. According to the severity and state of the database, your database may appear as in recovery mode, recovery pending, or suspect.
While restoring a backup or restarting the database, many SQL users encounter an issue where their database remains stuck in recovery mode instead of coming online. One such user query is shown below:
“I have SQL Server 2017 with a huge database. During a long-running transaction, the database got stuck in recovery mode after SQL Server restarted.”
A database can get stuck in recovery mode for various reasons. When this happens, you may be unable to restore your backup. In this article, we will discuss methods to fix the SQL Server database stuck in recovery mode issue.
Why SQL Server Database Stuck in Recovery Mode?
The SQL Server database may stuck in recovery mode due to one of the following reasons:
- Long-running transaction causing a delay in Redo phase
- Large-sized database file
- Damaged or missing log file (while restoring backup)
- Corruption in database
Methods to Resolve Database Stuck in Recovery Mode
The following are the troubleshooting methods that you can follow to resolve the database in recovery mode issue.
Method 1 – Wait for the Recovery Process Complete
When you restart the server, it follows a three-phase recovery process. If the long-running transactions were in progress before the server restart due to a system crash or failover, the redo phase re-applies the committed transactions that were not yet written to disk. It may result in database stuck in recovery mode. In such a case, you can wait until the recovery process completes, without restarting the system again as restarting the server may reset the recovery from scratch. To check the process of the recovery, you can take the help of EXEC xp_readerrorlog command. Here’s how to run this command:
sp_readerrorlog
[ [ @p1 = ] p1 ]
[ , [ @p2 = ] p2 ]
[ , [ @p3 = ] N’p3′ ]
[ , [ @p4 = ] N’p4′ ]
[ ; ]
Method 2 – Rebuild Transaction Log File
Your database may stuck in recovery mode due to corruption in transaction log file while restoring the backup file. The recovery process cannot complete due to missing or damaged log file. In such a case, you can restore transaction log backups. If there is no backup, then run the sp_attach_db command to rebuilt transaction log file. Alternatively, you can attach database without transaction log file.
Method 3 – Check and Repair SQL Database
Corruption is one of the reasons for the SQL Server database in recovery mode issue. You can run the DBCC CHECKDB command to check the database integrity. Here’s how to do so:
DBCC CHECKDB ‘database_name’;
If corruption is detected in the SQL database file, then you can use the DBCC CHECKDB command to repair the MDF file. To use this command, first make sure you have admin privileges on the database. Now, follow the steps below to repair the database:
- If your database is inaccessible, then you can set it in emergency mode. For this, use the following command:
ALTER DATABASE [Dbtesting] SET EMERGENCY
- Next, set the database to SINGLE_USER mode by using the command below:
ALTER DATABASE Dbtesting SET SINGLE_USER
- After that, use the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option as given below:
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
- Now, set the mode of database from SINGLE_USER to MULTI_USER by executing the command below:
ALTER DATABASE Dbtesting SET MULTI_USER
The DBCC CHECKDB command can repair the SQL database file, but it may not guarantee complete data integrity. For complete integrity and fast database repair, you can use a professional MS SQL repair tool. It can repair SQL database file (MDF/NDF) of any size and state without any data loss.
Conclusion
If the SQL Server database is in Recovery Mode, you can follow the above troubleshooting methods to bring the database online. If a missing transaction log file is the reason behind it, then you can rebuild the transaction log file or attach a SQL database without a transaction log file. If the database enters recovery mode due to corruption, then the best method is to repair the SQL database using an advanced SQL repair tool, like Stellar Repair for MS SQL. It can repair corrupt MDF/NDF file with complete integrity.
