SQL database Recovery Pending: causes and how to fix it

Applies to: SQL Server 2012 to 2022 and later on Windows, including Express edition. Replace YourDb in the examples with your database name, and take a copy of the database files before you try any repair.

What Recovery Pending means

Every time SQL Server starts a database, it runs crash recovery: it replays or rolls back transactions in the log so the data is consistent. A database is in Recovery Pending when recovery is needed but something stops it from even starting. It’s different from the other problem states:

• Recovering: recovery is running. Wait; on a big database with long transactions this can take a while.
• Recovery Pending: recovery couldn’t start, usually because of a resource problem such as a missing file or no disk space. The data may well be fine.
• Suspect: recovery started but failed, which often means real corruption in the data or log.

Check the current state with this query in SQL Server Management Studio (SSMS) or sqlcmd:

SELECT name, state_desc FROM sys.databases;

Step 1: Find the cause in the error log

Don’t guess: the SQL Server error log says exactly why recovery couldn’t start. In SSMS, expand Management > SQL Server Logs and open Current, or run:

EXEC sp_readerrorlog 0, 1, 'YourDb';

Look for messages around the time the database went into Recovery Pending. Common ones:

• Error 5120 or “Operating system error 5 (Access is denied)”: the SQL Server service account can’t open the file.
• Error 5120 or “Operating system error 2/3” (file or path not found): the file was moved, renamed or deleted, or its drive letter changed.
• Error 1105 or 9002: the disk or transaction log is full.
• Errors mentioning the log file is corrupt or inconsistent: the log is damaged; go to restore or emergency repair.

Step 2: Check the data and log files

List where SQL Server expects the files to be:

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDb');

• Make sure every file exists at that path. If a drive came back with a different letter (common with USB, SAN or cloud disks), change it back in Disk Management. If a file was moved on purpose, either move it back or point SQL Server at the new path with ALTER DATABASE [YourDb] MODIFY FILE (NAME = logical_name, FILENAME = 'new path'); and restart the SQL Server service.
• Free up disk space on the drives that hold the .mdf and .ldf files. If the drive is also slow or constantly busy, see 100% disk usage fixes.
• Check permissions. Right-click the folder, choose Properties > Security and make sure the SQL Server service account (for a default instance, NT SERVICE\MSSQLSERVER) has Full control. Files copied from another PC often lose this. Our guide to the folder permission error shows how to change ownership and access safely.
• Check antivirus and backup tools that may have locked the files at startup, and exclude the SQL data and log folders from real-time scanning.

Step 3: Bring the database online

Once the files are back and accessible, ask SQL Server to try recovery again:

ALTER DATABASE [YourDb] SET ONLINE;

If it succeeds, the state changes to ONLINE after recovery finishes. Then run a consistency check to make sure nothing was damaged:

DBCC CHECKDB ([YourDb]) WITH NO_INFOMSGS;

Restarting the SQL Server service also retries recovery for every database, but it disconnects all users, so prefer the command above on a busy server. If SET ONLINE fails, read the new error: it usually names the file that’s still the problem.

Step 4: Restore from a backup

If the log or data file is damaged or gone for good, restoring is the safe way back. Restore the last full backup, then any differential and log backups, ending with WITH RECOVERY. In SSMS, right-click Databases, choose Restore Database and pick your backup set. If you use the full recovery model and the data file is lost but the log survives, try a tail-log backup first so you lose as little as possible.

If the database files themselves were deleted from disk and you have no backup, stop writing to that drive and try a file recovery tool before anything else.

Last resort: emergency mode repair

Only use this when there’s no usable backup. REPAIR_ALLOW_DATA_LOSS can delete damaged pages and rebuild the log, so some data may be lost and transactions can be left half done. Stop the SQL Server service and copy the .mdf and .ldf files somewhere safe first, then start the service and run:

ALTER DATABASE [YourDb] SET EMERGENCY;
ALTER DATABASE [YourDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([YourDb], REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [YourDb] SET MULTI_USER;

Emergency mode makes the database read-only for members of sysadmin, so you can also use it just to copy out important tables before repairing. After the repair, check the application’s data carefully and take a full backup straight away.

How to stop it happening again

• Take regular full backups, plus log backups if you use the full recovery model, and test restoring them.
• Keep free space on the data and log drives, and set sensible autogrowth sizes so the log can’t fill the disk.
• Don’t move or rename database files while SQL Server is running; use ALTER DATABASE ... MODIFY FILE and a planned restart.
• Shut SQL Server down cleanly before Windows updates or power work, and use a UPS on physical servers.
• Schedule DBCC CHECKDB so corruption is found early, before a restart exposes it.

Related guides

• “You Don’t Currently Have Permission to Access This Folder”: How to Fix It
• 100% disk usage on Windows 11: how to find the cause and fix it
• Free file recovery on Windows 11 and 10: 4 ways to get deleted files back

Official sources: for more detail, see Microsoft’s own pages:

• Microsoft Learn: Database states in SQL Server
• Microsoft Learn: DBCC CHECKDB (Transact-SQL)
• Microsoft Learn: Troubleshoot a full transaction log (error 9002)

Frequently asked questions

Is my data lost when a database is in Recovery Pending?

Usually not. Recovery Pending normally means SQL Server couldn’t reach the files, not that they’re damaged. Fix the file, space or permission problem and the database typically comes back online with all its data.

Can I just detach and reattach the database?

It’s risky. A database that needs recovery may not reattach cleanly, and rebuilding the log during attach can lose recent transactions. Fix the cause and use SET ONLINE, or restore from backup, instead.

Why did it happen after a server restart?

Restarts are when SQL Server runs recovery, so problems that were already there, like a missing drive, full disk or changed permissions, only show up at startup.

How long does Recovering take compared with Recovery Pending?

Recovering is active work and finishes on its own; watch the error log for progress messages. Recovery Pending won’t change by itself until you fix what’s blocking it.