SQL Server Database In Recovery: Causes & Fixes

Our SQL Server support team handles the "database in recovery" call several times a month, and the pattern is consistent: the database is usually fine, and the most common damage is done by an administrator trying to hurry it. This page explains what the In Recovery state is, how to measure its progress, what is safe to do while it runs, and how to restore from a .bak file if you decide to go that route instead.

What "In Recovery" means

A SQL Server database shows In Recovery when the engine is running crash recovery against it. This happens after an unclean shutdown, a failover, a service restart while transactions were open, or a restore that has not yet been completed with RECOVERY. It is not an error state. It is the engine replaying the transaction log to bring the database to a consistent point.

Recovery runs in three phases, in order:

The phases are documented in Microsoft's transaction log architecture guide. The key point: recovery is proportional to the amount of log that must be processed, not to the size of the database. A 2 TB database with a clean log recovers in seconds. A 50 GB database with a 12-hour open transaction at shutdown can take hours.

How to check real progress

SQL Server writes percent-complete messages to the error log during each phase. Read them with:

EXEC xp_readerrorlog 0, 1, N'Recovery of database';

You will see lines such as "Recovery of database 'Sales' (7) is 34% complete (approximately 1200 seconds remain). Phase 2 of 3." The phase number tells you where it is; the estimate is recalculated as it runs and becomes more accurate over time.

You can also query the session performing recovery:

SELECT session_id, command, percent_complete,
       estimated_completion_time / 60000.0 AS est_minutes_remaining,
       wait_type, wait_time
FROM sys.dm_exec_requests
WHERE command IN ('DB STARTUP', 'RESTORE DATABASE', 'RESTORE LOG');

DB STARTUP is crash recovery; the RESTORE commands cover restores in progress. percent_complete is populated for these commands, per the sys.dm_exec_requests documentation. Run the query every few minutes and record the numbers. Increasing percent_complete means recovery is working. That record is also what tells you, later, whether it is stuck.

How long it takes

There is no fixed answer. The duration depends on measurable factors:

• Active log size at shutdown. Analysis and redo read the log from the last checkpoint forward. A long interval since the last checkpoint means more log to process.
• Open transactions at shutdown. Undo must reverse them completely. A bulk delete that had run for six hours before the crash will take a comparable amount of time to roll back. Rollback cannot be skipped.
• Virtual log file (VLF) count. The log file is divided internally into VLFs. A log that grew in thousands of small increments can contain tens of thousands of VLFs, and recovery must process the chain sequentially. High VLF counts add minutes to hours; see Microsoft's log architecture and management guide.
• Storage speed. Redo and undo are I/O-bound. Recovery on slow storage takes proportionally longer.

What not to do during recovery

Each of these actions is regularly attempted and each makes the situation worse:

• Do not restart the SQL Server service. Recovery restarts from the beginning. If it had run four hours, those four hours are discarded and the full process runs again.
• Do not detach the database. A database in recovery cannot be cleanly detached, and forcing it (offline, delete files, reattach) can leave the database in RECOVERY_PENDING or SUSPECT with no transactionally consistent state to return to. At that point your options narrow to restoring from backup or an emergency-mode repair that discards data.
• Do not kill the recovery session. The DB STARTUP session cannot be killed in any useful way, and killing a long rollback on a user session does not skip the rollback — the work still has to complete.
• Do not set the database offline or run ALTER DATABASE against it. The commands queue or fail, and taking it offline mid-recovery reintroduces the restart problem above.

Stuck versus slow

Slow recovery shows movement: percent_complete increases between checks, the error log posts new progress lines, and the estimated seconds remaining trend downward. That is normal, even when it takes hours.

Suspect it is actually stuck when:

• percent_complete has not changed across 30+ minutes of repeated checks, and
• the session's wait_type is a non-progress wait — for example a lock wait, or storage waits (PAGEIOLATCH_*) with wait_time climbing into minutes on a single wait, suggesting an I/O subsystem problem, and
• the error log has posted no new recovery lines in the same window, or is posting I/O errors (error 823/824) against the database files.

Stalls at exactly 0% in phase 1 with I/O errors in the log point at storage, not SQL Server. That is a case for checking the disk subsystem and, if the files are unreadable, going to backups. A recovery that is progressing at any measurable rate is not this case.

Restoring from a .bak file

If the database files are damaged, or recovery reveals corruption, the restore path replaces waiting. To restore a SQL database from a .bak file with T-SQL:

First, inspect the backup to confirm what it contains and where the files map:

RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales.bak';

Then restore. If this full backup is all you have and you want the database online immediately:

RESTORE DATABASE Sales
FROM DISK = N'D:\Backups\Sales.bak'
WITH MOVE N'Sales_Data' TO N'E:\Data\Sales.mdf',
     MOVE N'Sales_Log'  TO N'F:\Log\Sales_log.ldf',
     RECOVERY, STATS = 5;

If you have differential or log backups to apply after the full backup, restore the full with NORECOVERY, apply each subsequent backup with NORECOVERY, and finish the last one with RECOVERY:

RESTORE DATABASE Sales FROM DISK = N'D:\Backups\Sales_full.bak' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = N'D:\Backups\Sales_log1.trn' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = N'D:\Backups\Sales_log2.trn' WITH RECOVERY;

The distinction: WITH RECOVERY runs the undo phase and brings the database online — after which no further backups can be applied. WITH NORECOVERY leaves the database in the Restoring state, waiting for the next backup in the chain. Choosing RECOVERY too early is the common mistake; if you do it by accident, the entire restore sequence starts over from the full backup. The options are documented under RESTORE (Transact-SQL) and the overall procedure under restore a database backup using SSMS.

In SSMS: right-click Databases → Restore Database, select Device, add the .bak file, verify the backup sets shown, check the file paths on the Files page, and set the recovery state (RESTORE WITH RECOVERY or NORECOVERY) on the Options page. Uncheck "Take tail-log backup" only if the original database is gone; if it still exists and the log is intact, taking the tail-log backup first preserves the most recent transactions.

After any restore, run integrity checks before returning the database to service — our DBCC CHECKDB guide covers the procedure and how to read the output.

Related: transaction log full

A full transaction log (error 9002) is a different problem that often appears in the same incident, because a log that cannot grow can also block or lengthen recovery. First identify why the log cannot be truncated:

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'Sales';

The log_reuse_wait_desc value states the cause directly. The common ones: LOG_BACKUP means the database is in the FULL recovery model and no log backup has run — the fix is to take a log backup (or switch to SIMPLE if point-in-time recovery is genuinely not required). ACTIVE_TRANSACTION means an open transaction is pinning the log — find it with DBCC OPENTRAN and let it finish or roll it back. REPLICATION and AVAILABILITY_REPLICA mean a downstream consumer has not caught up. Microsoft's error 9002 troubleshooting page lists all values.

Backing up the log truncates it; shrinking it does not fix the cause. The order is: take the log backup (which frees internal space), and only then, if the physical file has grown far beyond its working size because of a one-off event, shrink it once with DBCC SHRINKFILE to a sensible target and leave it there. Shrinking a log that will regrow to the same size accomplishes nothing except regrowth events and VLF fragmentation. A scheduled shrink job is a defect, not maintenance.

Preventing long recoveries

• Keep VLF counts sane. Check with SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID()). Under roughly 1,000 VLFs is unremarkable; tens of thousands is a problem. Fix by shrinking the log once and regrowing it in a few large increments.
• Size the log deliberately. Set the file to its observed working size with a fixed growth increment (for example 512 MB–1 GB), not a percentage. Percentage growth on a large log creates enormous growth events and uneven VLFs.
• Back up the log on a schedule matched to your data-loss tolerance in FULL recovery model. This keeps the active log short, which is the single largest factor in crash-recovery time.
• Avoid giant single transactions. Batch large deletes and updates. The open transaction at the moment of a crash defines the undo work.
• Set target recovery time. ALTER DATABASE ... SET TARGET_RECOVERY_TIME = 60 SECONDS enables indirect checkpoints, which bound the redo work; this is the default on databases created on SQL Server 2016 and later.

If the database is down right now

Recovery incidents do not schedule themselves for business hours, so Microsoft SQL Server support at Medha Cloud runs 24/7. A DBA is available on live chat at any hour; typical engagement on an in-recovery or restore case starts within minutes of contact. We handle assessment (progressing, stuck, or corrupt), restore-chain construction, and post-restore integrity verification.