Fix SQL Server Database in RECOVERY PENDING State

The most common cause. If the transaction log file has been deleted, moved to a different drive, or renamed while SQL Server was offline, the engine cannot find it at the registered path and immediately places the database in RECOVERY PENDING. The same applies to the primary data file (.MDF) if it is unavailable.

SQL Server needs to write log records even during the recovery process itself. If the volume hosting the .LDF file has no free space, SQL Server cannot create or extend the log file and will refuse to begin recovery, leaving the database in RECOVERY PENDING until space is freed.

If the SQL Server service account loses read/write permission on the data or log files — often after an OS upgrade, domain policy change, or manual permission reset — it cannot open those files and the database enters RECOVERY PENDING. This is a silent killer: the files are physically intact, but inaccessible to the service.

When the SQL Server service account is changed without properly granting the new account access to the database files and directories, SQL Server starts successfully but cannot open the specific database files owned by the old account. This results in RECOVERY PENDING for affected databases.

The SQL Server Error Log is your single most valuable diagnostic tool. It will tell you exactly why recovery cannot start. Open SSMS, navigate to Management → SQL Server Logs → Current, or run the following T-SQL:

-- Read the SQL Server Error Log (most recent 200 entries)
EXEC xp_readerrorlog 0, 1, N'RECOVERY PENDING';
EXEC xp_readerrorlog 0, 1, N'Error';
EXEC xp_readerrorlog 0, 1, N'cannot be opened';

Look for messages mentioning your database name, file paths, access denied errors, or disk full warnings. This message will guide everything that follows.

Confirm what file paths SQL Server has registered for the affected database and then manually verify those files exist on disk at exactly those paths:

-- Check registered file paths for all databases
SELECT
    DB_NAME(database_id) AS DatabaseName,
    name                 AS LogicalName,
    physical_name        AS PhysicalPath,
    type_desc           AS FileType,
    state_desc           AS [State]
FROM sys.master_files
WHERE state_desc = N'RECOVERY_PENDING'
ORDER BY database_id;

Open Windows Explorer and navigate to each path shown in PhysicalPath. If a file is missing, restore it from backup or use Data Repair Pro to attach with only the MDF. If the path is simply wrong (file was moved), update it with ALTER DATABASE … MODIFY FILE while the database is offline.

If the error log reports an access denied error, grant the SQL Server service account full control. Open a Command Prompt as Administrator and run:

-- Replace paths and service account as needed
icacls "D:\SQLData\YourDatabase.mdf" /grant "NT SERVICE\MSSQLSERVER:(F)"
icacls "D:\SQLLogs\YourDatabase_log.ldf" /grant "NT SERVICE\MSSQLSERVER:(F)"

-- If you use a named instance, replace MSSQLSERVER with the instance name:
icacls "D:\SQLData\YourDatabase.mdf" /grant "NT SERVICE\MSSQL$INSTANCENAME:(F)"

After granting permissions, restart the SQL Server service via SSMS or Services.msc. The database should come ONLINE automatically if permissions were the only cause.

If the drive hosting the .LDF file is full, use Windows Disk Cleanup, delete unnecessary files, or move the transaction log to a volume with sufficient free space. As a minimum, the log volume needs at least 10–20% free space of the current log file size, plus room to grow. After freeing space, restart SQL Server. You do not need to run any T-SQL — SQL Server will automatically retry recovery on restart.

If steps A–D did not resolve the issue and the error log indicates file corruption, you can force the database into EMERGENCY mode. This bypasses the normal recovery sequence and opens the database in a read-only, restricted state, allowing you to extract data:

-- Force EMERGENCY mode (run in master context)
ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
GO

-- Verify state
SELECT name, state_desc FROM sys.databases
WHERE name = N'YourDatabaseName';

Warning: EMERGENCY mode should only be used when the database files are present but damaged, and only after all non-corrupt remedies have been exhausted. Do not attempt writes in this mode.

Once in EMERGENCY mode, set the database to SINGLE_USER and run DBCC CHECKDB. Use REPAIR_REBUILD first; escalate to REPAIR_ALLOW_DATA_LOSS only if necessary, as it may discard some data:

-- Step 1: Switch to single-user mode
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Step 2: Try non-destructive repair first
DBCC CHECKDB (N'YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS;
GO

-- Step 3: If errors remain, allow data loss repair (LAST RESORT)
DBCC CHECKDB (N'YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

-- Step 4: Bring back to MULTI_USER
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
GO