SQL Server 'Database in Recovery Pending' Fix Guide

You're staring at SQL Server Management Studio, and your database has that dreaded '(Recovery Pending)' status. Your app is down, users are yelling, and you need this fixed yesterday. I've been there. Usually, it's one of three things: a disk full, a log file gone rogue, or a corrupted page. Let's walk through each fix in order of how often I see them.

1. Disk Full — The #1 Cause

More than half the time, 'Recovery Pending' happens because the drive where your database or log file sits has run out of space. SQL Server can't complete its crash recovery because it needs room to roll forward or roll back transactions. I had a client last month whose entire print queue died because of this — their C: drive was at 0 bytes after a backup job ran wild.

How to check:

-- Check disk space via xp_fixeddrives
EXEC xp_fixeddrives;

-- Or check log file size and growth
USE master;
SELECT name, physical_name, size * 8 / 1024 AS SizeMB FROM sys.master_files WHERE database_id = DB_ID('YourDBName');

The fix: Free up space or move the files. If you can't free space fast, move the log file to a different drive:

ALTER DATABASE YourDBName SET OFFLINE;
-- Move the file physically (copy, don't cut)
ALTER DATABASE YourDBName MODIFY FILE (NAME = YourDB_log, FILENAME = 'D:\NewPath\YourDB_log.ldf');
ALTER DATABASE YourDBName SET ONLINE;

If you're stuck and need it online right now, you can set it to emergency mode to read data, then fix the space issue:

ALTER DATABASE YourDBName SET EMERGENCY;
ALTER DATABASE YourDBName SET SINGLE_USER;
-- Now you can query it (read-only) but it's not fully recovered

After freeing space, run ALTER DATABASE YourDBName SET ONLINE to kick off full recovery.

2. Log File Corruption or Auto-Growth Failure

Sometimes the disk isn't full, but the transaction log file itself is corrupted or auto-growth failed mid-transaction. SQL Server panics and marks the database as recovery pending. This happens a lot when you've set the log to auto-grow by 10% instead of a fixed MB size — it grows in small, fragmented chunks and eventually hits a corrupted segment.

The real fix: Don't bother with shrink commands here. You need to force recovery with truncation. Run this in single-user mode:

ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS);
-- Yes, that flag means what it says. You may lose some data.
ALTER DATABASE YourDBName SET MULTI_USER;

If that fails, you can try a more targeted repair on just the log:

ALTER DATABASE YourDBName SET EMERGENCY;
ALTER DATABASE YourDBName SET SINGLE_USER;
DBCC CHECKDB ('YourDBName', REPAIR_REBUILD);
ALTER DATABASE YourDBName SET ONLINE;
ALTER DATABASE YourDBName SET MULTI_USER;

If the log is completely toast, create a new log by detaching and reattaching with a fresh log:

EXEC sp_detach_db 'YourDBName', 'true';
-- Delete the corrupt .ldf file
CREATE DATABASE YourDBName ON (FILENAME = 'C:\Data\YourDBName.mdf') FOR ATTACH_REBUILD_LOG;

This forces SQL Server to build a new log from scratch. You'll lose the transaction history, but the data stays.

3. Corrupted Page or Index — The Uncommon One

This one's rarer but nasty. A single bad page can trip up recovery. You'll usually see it after a sudden power loss or a failed disk. The error in the SQL log will show something like 'Page (1:3456) is missing a checksum'.

How to find the bad page:

DBCC CHECKDB ('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Look for lines saying 'repair_allow_data_loss' or 'repair_rebuild'. If it's just a non-clustered index, you can rebuild that index directly instead of running a full repair:

ALTER INDEX ALL ON dbo.YourTable REBUILD;

If it's a clustered index or a page in the data itself, you're looking at:

DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS);

This will delete the corrupt page and its rows. You'll lose that data, but the rest of the database will come back online. After that, restore from a backup for the missing rows if you can.

Prevent this in the future: Enable page checksums and torn page detection on all databases:

ALTER DATABASE YourDBName SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE YourDBName SET TORN_PAGE_DETECTION ON;

And for heaven's sake, set your log auto-growth to a fixed MB (like 100 MB) instead of a percentage. Percentages cause fragmentation.

Quick-Reference Summary

Bottom line: Start with disk space. 8 times out of 10, that's it. If not, go for the log. And always, always have a backup before you run repairs — you've been warned.