SQL Server database in SUSPECT or RECOVERY_PENDING: an offline database and how to recover it

A production database is showing state_desc = SUSPECT or RECOVERY_PENDING in sys.databases. Applications cannot open connections to that database. Users are seeing login failures, query timeouts, or generic “database cannot be opened” errors. The SQL Server instance itself is up, and every other database on it may be fine.

RECOVERY_PENDING rarely means corruption. It usually means SQL Server could not get the resources it needed during recovery: a missing file, a full log volume, a permissions change, or a transient I/O failure at startup. SUSPECT is more serious because recovery actually ran and failed, but it still does not automatically mean data loss. The wrong move is to jump straight to DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. The right move is to fix the underlying resource, re-run recovery, and only fall back to repair or restore when that fails.

This article walks through the operator recovery path for SUSPECT, RECOVERY_PENDING, and EMERGENCY, with the gotchas that cost time during a real incident.

What this means

SQL Server exposes database state through sys.databases.state_desc. The three states that matter here are:

• RECOVERY_PENDING - SQL Server encountered a resource-related error during recovery and could not process the transaction log. The database itself is not necessarily damaged. Files may be missing, the log volume may be full, or system-level resource limits may have prevented startup. Recovery has not run to completion.
• SUSPECT - Recovery ran and failed. At least the primary filegroup is suspect and may be damaged. SQL Server cannot bring the database online. This is more serious than RECOVERY_PENDING because recovery attempted and could not finish, often pointing at I/O-level corruption.
• EMERGENCY - A manually set state used for repair. The database becomes read-only, single-user, logging is disabled, and only sysadmin members can connect. You set this deliberately; it is not a state SQL Server enters on its own.

Do not confuse any of these with RECOVERING, which is the normal transient state every database passes through after a restart. Large databases legitimately sit in RECOVERING for minutes or longer, depending on size, VLF count, and workload. RECOVERING is automatic. RECOVERY_PENDING is stuck and requires user action.

One well-known gotcha: DATABASEPROPERTYEX(db, 'Status') returns 'SUSPECT' when the database is actually in RECOVERY_PENDING. This behavior has persisted across versions. Always use sys.databases.state_desc for the authoritative state, not DATABASEPROPERTYEX.

Common causes

Quick checks

Run these read-only checks before changing anything. They tell you whether you have a recoverable resource problem or actual corruption.

-- 1. Authoritative state of every database
SELECT name, state_desc, user_access_desc, is_read_only, is_in_standby,
       recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE state_desc <> 'ONLINE';

-- 2. Free space on volumes hosting database files
SELECT DISTINCT
    vs.volume_mount_point,
    vs.total_bytes / 1048576 AS total_mb,
    vs.available_bytes / 1048576 AS available_mb,
    CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,2)) AS pct_free
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY pct_free;

-- 3. Database file paths and types
SELECT DB_NAME(database_id) AS db_name, file_id, type_desc, name AS logical_name,
       physical_name, state_desc, size * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE DB_NAME(database_id) IN (
    SELECT name FROM sys.databases WHERE state_desc <> 'ONLINE'
);

-- 4. Persistent I/O corruption record (survives restarts)
SELECT database_id, DB_NAME(database_id) AS db_name,
       file_id, page_id, event_type, error_count, last_update_date
FROM msdb.dbo.suspect_pages
WHERE event_type IN (1, 2, 3);  -- 1=823 or 824 other than bad checksum/torn page, 2=bad checksum (824), 3=torn page (824)

-- 5. Recent critical I/O errors in the SQL Server error log
EXEC sp_readerrorlog 0, 1, 'Error: 823';
EXEC sp_readerrorlog 0, 1, 'Error: 824';
EXEC sp_readerrorlog 0, 1, 'Error: 825';
EXEC sp_readerrorlog 0, 1, 'Error: 9002';

-- 6. SQL Server build (for known-bug exposure on AG setups)
SELECT SERVERPROPERTY('ProductVersion') AS build,
       SERVERPROPERTY('ProductLevel') AS product_level,
       SERVERPROPERTY('Edition') AS edition;

On Windows, also check the Application and System event logs around the last service restart for storage controller, NTFS, or Filter Manager events. On Linux, check journalctl and dmesg for SCSI or filesystem errors against the underlying devices.

How to diagnose it

flowchart TD
    A[sys.databases state_desc not ONLINE] --> B{RECOVERING?}
    B -- yes --> C[Wait. Large DBs take minutes.]
    B -- no --> D{RECOVERY_PENDING or SUSPECT?}
    D -- RECOVERY_PENDING --> E[Read error log for resource error]
    D -- SUSPECT --> F[Read error log for recovery failure]
    E --> G{Resource fixable?
disk, file, perms, AV}
    G -- yes --> H[Fix resource, then ALTER DATABASE SET ONLINE]
    G -- no --> I[Restore from backup]
    F --> J{Errors 823/824/825 or suspect_pages?}
    J -- yes --> I
    J -- no --> K[EMERGENCY + DBCC CHECKDB]
    H --> L[Back online]
    I --> L
    K --> M{Repair needed?}
    M -- no --> H
    M -- REPAIR_ALLOW_DATA_LOSS --> N[Last resort. Accept data loss.]

1. Confirm the real state. Query sys.databases.state_desc rather than relying on DATABASEPROPERTYEX, tools, or what the application reported. RECOVERY_PENDING and SUSPECT have different recovery paths.
2. Find the timestamp in the error log. Look for the recovery attempt that failed. The entries immediately before the failure usually name the file, the OS error code, or the page that could not be read.
3. Check OS resources first. Disk space, file presence, and service account ACLs are the most common causes and the cheapest to fix. If a volume was full at startup and is now clear, recovery may succeed as soon as you bring the database online.
4. Check for I/O corruption. Look for errors 823 (hard I/O error), 824 (logical consistency error), and 825 (read retry succeeded, the disk is failing) in the error log, and check msdb.dbo.suspect_pages for durable records. Any of these moves you toward restore, not in-place repair.
5. Check the AG context. If the database is in an availability group, the recovery path changes. You cannot drop, restore, or run ALTER DATABASE SET HADR OFF against a database that is SUSPECT or RECOVERY_PENDING on the primary. You must fail over, remove the replica from the AG, then recover the database on the now-isolated instance.
6. Check the build number against known bugs. SQL Server 2022 before CU22 has a bug where AG databases enter SUSPECT instead of RESOLVING when a replica is removed while offline. SQL Server 2019 before CU12 has a bug where VSS backups freeze I/O on AG secondaries, causing RECOVERY_PENDING after the backup window.
7. Decide between fix-resource, restore, and repair. Fix the resource first. Restore from backup if there is corruption. Reserve EMERGENCY plus DBCC CHECKDB for the case where no good backup exists.

Metrics and signals to monitor

Fixes

Fix the underlying resource first

For RECOVERY_PENDING caused by a missing file, full disk, locked file handle, or permissions change, fix the resource and bring the database back online. Do not enter EMERGENCY mode for a resource problem.

-- After freeing space, restoring the file path, or fixing ACLs:
ALTER DATABASE [YourDB] SET OFFLINE;
ALTER DATABASE [YourDB] SET ONLINE;

The OFFLINE to ONLINE cycle forces SQL Server to re-attempt recovery. If the original cause was transient (a temporarily unavailable L drive for transaction logs, an AV minifilter that released its handle, a brief disk-full condition), the database will recover normally. No DBCC CHECKDB, no repair, no data loss.

Restore from backup (preferred for SUSPECT with corruption)

If the error log shows 823, 824, or 825 errors, or suspect_pages has new rows for this database, restore is safer than repair. Repair with REPAIR_ALLOW_DATA_LOSS does what its name says. Restoring from a validated backup chain gives you a known-consistent database.

Make sure log backups have been running so you can recover to a point close to the failure. If RESTORE VERIFYONLY has never been run against these backups, do it now; a backup that cannot be restored is not a backup.

EMERGENCY plus DBCC CHECKDB (last resort)

If no good backup exists, the final diagnostic and recovery path is EMERGENCY mode plus DBCC CHECKDB. This is the very last resort, not the first.

-- Destructive path. Use only when restore is not available.
ALTER DATABASE [YourDB] SET EMERGENCY;
ALTER DATABASE [YourDB] SET SINGLE_USER;
DBCC CHECKDB ([YourDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

CHECKDB will report whether repair is possible and what level of repair is required. Only if it tells you REPAIR_ALLOW_DATA_LOSS is needed should you consider running:

-- This WILL lose data. Last resort only.
DBCC CHECKDB ([YourDB], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Two constraints to remember. Rebuilding the log is not supported for databases that contain MEMORY_OPTIMIZED_DATA filegroups, so REPAIR_ALLOW_DATA_LOSS may not be available on those databases. And EMERGENCY-mode repair was designed as a recovery mechanism of last resort, not as routine maintenance.

AG-specific recovery

If the affected database is an AG primary in SUSPECT or RECOVERY_PENDING, you cannot drop, restore, or remove it from the AG in place. You must fail over to a healthy synchronous secondary, remove the damaged replica from the AG, and then recover the database on the isolated instance using one of the paths above.

If the issue is on an AG secondary and your build is below SQL Server 2019 CU12, apply the CU. The bug that leaves AG secondaries in RECOVERY_PENDING after VSS backups was fixed in SQL Server 2019 CU12 (KB5004524). If your build is SQL Server 2022 below CU22 and you are removing offline replicas, apply KB5068450 to stop databases entering SUSPECT instead of RESOLVING.

What not to do

• Do not detach the database. sp_detach_db against a SUSPECT or RECOVERY_PENDING database fails with “Cannot detach a suspect or recovery pending database. It must be repaired or dropped.”
• Do not restart the instance as a first response. A restart recycles the error log and loses the recovery context that tells you why recovery failed. It also does not fix a missing file or a full disk.
• Do not jump to REPAIR_ALLOW_DATA_LOSS. Exhaust the resource fix and the restore path first. The repair option allocates pages, deallocates objects, and rebuilds the log as needed to make the database structurally consistent, which is not the same as making the data correct.
• Do not run DBCC CHECKDB with repair in EMERGENCY mode without first capturing the current state of the error log, suspect_pages, and any msdb backup history. You will want that evidence for the post-incident review.

Prevention

• Pre-size data and log files. Autogrow is expensive and log autogrow cannot use Instant File Initialization. A log volume that hits 100 percent at startup is one of the most common RECOVERY_PENDING triggers.
• Monitor free space on every volume hosting database files. Treat below 20 percent as a ticket and below the next autogrow increment as a page.
• Run DBCC CHECKDB on a weekly schedule for production databases. Many forms of corruption are only detected by CHECKDB, and finding corruption during a scheduled job is far better than finding it during a restart recovery.
• Validate backups with restore tests. RESTORE VERIFYONLY is a minimum; actual restores on a non-production system are the gold standard. A SUSPECT database with no tested backup leaves you with REPAIR_ALLOW_DATA_LOSS as the only option.
• Stay current on cumulative updates, especially on instances hosting availability groups. SQL Server 2019 CU12 and SQL Server 2022 CU22 both fix bugs that directly cause RECOVERY_PENDING and SUSPECT states in AG setups.
• Review antivirus and backup minifilter behavior. SQL Server file directories should be excluded from AV scans, but minifilter drivers can still intercept IRPs regardless of exclusions. If RECOVERY_PENDING recurs after service restarts and the error log shows OS error 32 from PID 4, the Filter Manager is the prime suspect.
• Monitor msdb.dbo.suspect_pages. Any new row is a warning. The table is durable across restarts and captures errors 823, 824, and torn page detection that may have been missed in the rolling error log.

How Netdata helps

• Per-second polling of sys.databases.state_desc catches the transition into SUSPECT, RECOVERY_PENDING, or EMERGENCY as it happens, rather than waiting for an application team to report failed connections.
• Disk space on the volumes hosting database files is tracked directly through sys.dm_os_volume_stats, so the most common RECOVERY_PENDING cause (a full log or data volume) is visible in the same window as the state change.
• The SQL Server error log is the primary diagnostic source for recovery failures; search it with sp_readerrorlog for errors 823, 824, 825, and 9002 to correlate a SUSPECT transition with the underlying I/O failure that caused it.
• For availability groups, replica state and synchronization health are tracked alongside database state, which is critical for diagnosing AG-related RECOVERY_PENDING on secondaries.
• The SQL Server build number is inventoried, so you can see at a glance whether an instance is exposed to the known VSS or AG-removal bugs that cause these states.
• Because metrics are retained at per-second granularity, the restart window and the moments before recovery failed can be replayed during the post-incident review without relying on the recycled error log.

See Microsoft SQL Server monitoring with Netdata for the full integration.

Related guides

• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query
• SQL Server Error 823 and 824: I/O and logical consistency errors
• SQL Server Error 825: read-retry succeeded and the disk is failing
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators