What’s the issue?
Every SQL Server database has a state that reflects its current operational condition. Several other states exist that indicate the database is in an abnormal condition: SUSPECT, EMERGENCY, RECOVERY_PENDING, RESTORING, and COPYING.This finding flags databases that are in a state other than ONLINE or OFFLINE. Each of these unusual states points to a specific situation, ranging from in progress operations that should resolve on their own to serious failures that require immediate attention.
Why is this a problem?
Each unusual state has different implications and warrants investigation, since the database is not available for normal use while in any of these states.SUSPECT means SQL Server attempted recovery during startup but failed, typically due to corruption, missing files, or storage failures. The database is inaccessible, and the underlying cause must be identified before recovery actions are taken to avoid making the situation worse.
RECOVERY_PENDING means SQL Server cannot start recovery, often because of missing files, permission problems, or storage that was unavailable at startup. The database is also inaccessible until the underlying issue is resolved.
EMERGENCY is an administrator initiated state used during recovery from corruption. It allows a sysadmin to access an otherwise unrecoverable database in read only mode for diagnosis or to perform repair operations such as DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. A database left in EMERGENCY mode unintentionally is not protected and is vulnerable to further damage.
RESTORING is normal during a restore operation but indicates a problem if the database remains in this state for an extended period without an active restore in progress. This commonly happens when a restore was initiated WITH NORECOVERY for log shipping or staging purposes and the recovery step was never completed.
COPYING applies to Azure SQL Database and Managed Instance and indicates a copy operation is in progress, but its presence on a traditional SQL Server instance is unusual and warrants investigation.
In all of these cases, the database is unavailable to applications, and the unusual state often signals an underlying problem with storage, files, permissions, or process execution that should be addressed before more damage occurs.
What should you do about this?
Identify affected databases by querying sys.databases and reviewing the state_desc column for any value other than ONLINE or OFFLINE. For each one, determine the specific state and investigate the cause before taking action.For SUSPECT and RECOVERY_PENDING databases, review the SQL Server error log for the specific error encountered during recovery, then follow the standard suspect database recovery process: address the underlying cause (corruption, missing files, storage), restore from a clean backup if possible, or use EMERGENCY mode and DBCC CHECKDB as a recovery path of last resort.
For EMERGENCY databases, confirm the state was set intentionally as part of an active recovery effort. If the recovery is complete, return the database to MULTI_USER and ONLINE state with ALTER DATABASE [DatabaseName] SET MULTI_USER; and ALTER DATABASE [DatabaseName] SET ONLINE;. If the database has been in EMERGENCY mode for an extended time without active work, escalate to determine whether recovery should resume or whether the database should be replaced from backup.
For RESTORING databases that are not part of an active restore, log shipping setup, or Always On configuration, complete the restore with RESTORE DATABASE [DatabaseName] WITH RECOVERY; to bring it online. If the database was being staged for a process that has been abandoned, decide whether to complete the restore or drop the database.