Skip to content

database-states.md

Latest commit

 

History

History
55 lines (48 loc) · 4.05 KB

database-states.md

File metadata and controls

55 lines (48 loc) · 4.05 KB
title Database States
description Find out about various database states, such as ONLINE, OFFLINE, or SUSPECT. Learn how to verify the current state of a database.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer randolphwest
ms.date 10/20/2025
ms.service sql
ms.subservice configuration
ms.topic concept-article
ms.custom
ignite-2025
f1_keywords
SQL13.SWB.DATABASESTATES.F1
helpviewer_keywords
emergency database state [SQL Server]
verifying database states
viewing database states
displaying database states
database states [SQL Server]
current database state
offline database state [SQL Server]
suspect database state
recovery pending database state [SQL Server]
states [SQL Server], databases
online database state
states [SQL Server]
restoring database state [SQL Server]
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Database states

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW FabricSQLDB]

A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view or the Status property in the DATABASEPROPERTYEX function.

Database state definitions

The following table defines the database states.

State Definition
ONLINE Database is available for access. The primary filegroup is online, although the undo phase of recovery might not have been completed.
OFFLINE Database is unavailable. A database becomes offline by explicit user action and remains offline until further user action is taken. For example, the database might be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING Database is being recovered. The recovering process is a transient state; the database automatically becomes online if the recovery succeeds. If the recovery fails, the database becomes suspect. The database is unavailable.
RECOVERY PENDING [!INCLUDE ssNoVersion] has encountered a resource-related error during recovery. The database isn't damaged, but files might be missing or system resource limitations might be preventing it from starting. The database is unavailable. Further action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT At least the primary filegroup is suspect and might be damaged. The database can't be recovered during startup of [!INCLUDE ssNoVersion]. The database is unavailable. Further action by the user is required to resolve the problem.
EMERGENCY User has changed the database and sets the status to EMERGENCY. The database is in single-user mode and might be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

Related content