What is the Difference between SUSPECT and RECOVERY PENDING? – Interview Question of the Week #114

Interview question: What is the difference between RECOVERY PENDING and SUSPECT in SQL Server?

Answer: RECOVERY PENDING means a resource problem has prevented recovery from proceeding. SUSPECT means recovery failed and at least the primary filegroup is suspect. Neither label tells you the exact cause; read the SQL Server error log first.

A reader once messaged me after moving all user databases to another drive. After restarting SQL Server, one database showed RECOVERY PENDING. I asked for the error log. It showed error 5120 for a log file on the new drive, with Windows operating system error 5: access denied. We checked the SQL Server service identity in Configuration Manager, corrected its access to the new file location, and the database recovered. That real incident is why I start with the error, not the state name alone.

The two states point to different stages of trouble:

• RECOVERY PENDING: Required files, permissions, storage, or another resource may be unavailable. SQL Server cannot complete the work needed to bring the database online. The database is not necessarily damaged.
• SUSPECT: SQL Server could not recover the database during startup, and at least its primary filegroup is suspect. Corruption or a serious I/O failure may be involved, but the state is not a diagnosis by itself.

These read-only queries identify affected databases and their configured file paths:

SELECT name, state_desc
FROM sys.databases
WHERE state_desc IN (N'RECOVERY_PENDING', N'SUSPECT');

SELECT DB_NAME(database_id) AS database_name,
       name AS logical_file_name,
       physical_name,
       state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'YourDatabase');

Compare the paths with the actual files on the SQL Server machine and read the related error-log entries. Preserve the existing files and backups while diagnosing the cause. A missing permission calls for a permission correction, not an emergency repair command. A damaged database needs a recovery plan based on the errors and available backups. Microsoft’s database-state definitions make this distinction explicit.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.