How to Troubleshoot SQL Server Database in Recovery?

Different Phases of SQL Server Database Recovery

Recovering a SQL server database from failure requires a database administrator to restore SQL server backup in a logical and meaningful sequence. While effectively moving from recovery to normal mode, the database, data file, and data page are used to restore and recover the supporting data. The recovery of each database consists of three phases that include:

• Analysis Phase

This phase thoroughly analyses the transaction log, determines the last checkpoint, and creates an Active Transaction Table (ATT) and Dirty Page Table (DPT). ATT has a record of all active transactions indicating when the database was not properly shut down. In comparison, DPT records all the dirty pages indicating when the database was shut down.

• Redo Phase

This phase forwards all the modifications recorded in the log and cannot write the data files while shutting the database down. The DPT has a minimum log sequence (minLSN) required to successfully recover the database and initiate the redo operation required for all dirty pages.

• Undo Phase

This phase holds all the incomplete transactions found in ATT and ensures the preservation of the database. After the successful rollback, the database becomes online, where you must apply no additional transaction log backups to the database.

Why Does SQL Server Go into Recovery?

To fix and roll out the issue, it’s always necessary to find the reasons behind errors and disruptions. There are many reasons behind the pending state of SQL Server database in recovery. It might be that the database is not correctly shut down, indicating that an uncommitted transaction that was active at the time of shutdown resulted in the deletion of the log file.

The insufficiency of space in the hard disk can be another reason you couldn’t initiate the recovery database SQL Server. Moreover, the corrupted files in the database also cause problems for users.

Here are some key reasons that cause SQL servers to go into recovery mode:

1. When a long-running transaction is causing disruption.
2. When the size of the transaction log file is huge.
3. There are multiple Virtual Log Files (VLFs) inside the DB transaction log.
4. There is a giant bug in the SQL server that you later fixed.

How to Fix SQL Server in Recovery Mode Issue?

Ensure that there is enough backup of the database in recovery and the SQL server database in recovery check progress while starting the recovery process. Troubleshooting the SQL server database requires a thorough and detailed investigation of the SQL server error log. There are multiple methods to fix SQL server in recovery mode issues:

Method 1: Manual Restore

There are two steps to bring the SQL Server database in recovery mode to normal mode.

1. Initiate forceful repair by marking the database in emergency mode

EMERGENCY mode marks the database as READ ONLY and disables grant and logging access to the system administrator. In this mode, the setting is done in such a setting that brings back inaccessible databases online. Once the database is opened in EMERGENCY mode, one should repair the database by using the DBCC CHECKDB command.

2. Detach and reattach the database by marking the database in emergency mode

This mode also calls for marking databases but in EMERGENCY mode. After that, detach (offline) the database and bring it back online (re-attach).

Method 2: Repair Option/ Forceful Repair

The repair option is used as a last resort when no other repairing option works correctly. Microsoft always recommends using a good backup as a primary method to restore and recover errors reported in DBCC CHECKDB.

The repair options in DBCC CHECKDB make it ultimately recoverable. Always use CHECKDB with repair options to roll back repair options successfully.

There are multiple repairing levels with almost zero possibility of data loss.

• Set the database to single-user mode
• Run DBCC CHECKDB with ‘REPAIR_BUILD’
• Reactivate multiple user modes by setting the database back to online.

The database is mainly recovered; otherwise, ‘REPAIR_ALLOW_DATA_LOSS’ is preferred. Be sure that there is proper backup and risks are calculated.

• Set the database to emergency mode
• Now run DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’
• Reactivate multiple users mode and set the database back to online.

Method 3: Detach and Reattach SQL Server Database

This is another preferred mode of recovering databases in recovery mode to normal mode. This mode enables the database to go into emergency mode, and once the data becomes accessible, it is taken offline (detached) and brought back online (re-attach).

Method 4: Use an SQL Server Recovery Tool

Many SQL Server recovery tools recover data from getting corrupt and provide a seamless solution for damaged SQL servers. The DBR for SQL Server recovery tool first examines and scans the damaged files and then shows the data preview that you can restore. It also enables the user to recover the deleted records from corrupt files and save them in a separate SQL Server file.

Now, you can get started with our easy-to-use DBR software as below.

1. Select the data file path from the recovery pop-up. Make sure Recover deleted data is checked before you click on Recover to begin the process.

2. Under the System Table, the tree will show you the name, status, total records, normal records, and deleted records in your recovered databases. Normal table records are shown with a green tick mark, while the deleted tables are shown with a purple cross.

Conclusion

It’s understandable that troubleshooting an SQL server database in recovery after restart is hectic. Most of the SQL server databases can be stuck in recovery mode due to various reasons, including but not limited to an uncommitted transaction during a server crash, shutting down an unclean system, or inadequate space issues.

The database needs to go through a complete recovery process to access data files. Before initiating the recovery process, be sure about the reasons behind the errors and try to fix them manually.