Different States of SQL Server Database


SQL Server Database can reside in one among the seven states. For example these can be ONLINE, OFFLINE, RESTORING, SUSPECT etc. This article explains each of these states and includes a TSQL code which can be used to identify the current state of a SQL Server Database.

A SQL Server Database is can only be in one specific state at a given time.

Different States of SQL Server DatabaseDifferent States of SQL Server Database are:

ONLINE
When a database is in ONLINE state the database is available for access. The primary filegroup is online even though the undo phase of recovery may not have been completed.

T-SQL to set the database status to ONLINE

USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO

OFFLINE
When a database is in OFFLINE state then the database is not accessible for user connections. One can set the database to this state if you don’t want users to connect to the database. For example you have migrated the database to a new server and don’t want users to accidentally connect to the Old SQL Server Database. Or, the database may 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.

T-SQL to set the database status to OFFLINE

USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORING
When a database is in RESTORING state then it means one or more files of the primary filegroup is been restored or one or more secondary files are being restored offline.

RECOVERING
When a database is in RECOVERING state it means its in the process of recovery and it will become automatically ONLINE for user connectivity. In case of a failure the database will become SUSPECT and become unable for use until a database intervene and fixes the issues.

RECOVERY PENDING
When a database is in RECOVERY PENDING state it means SQL Server has encountered a resource related error during recovery. The database might be missing files. DBAs intervention is required in such a case.

SUSPECT
When a database is in SUSPECT state it means the database is unavailable for user connection. Database may be damaged or at least the primary filegroup is supect. DBAs intervention is required in such a case. Read the following article which explains “How to Repair SUSPECT Database in SQL Server”

EMERGENCY
When a database is in EMERGENCY state it means a user has changed the status of the database to EMERGENCY. In EMERGENCY mode database will remain in SINGLE_USER mode and the database can be repaired or restored. Database will remain READ_ONLY. 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.

T-SQL to set the database status to EMERGENCY

USE master
GO
ALTER DATABASE YourDatabaseName 
SET EMERGENCY
GO

How to Identify Current State of SQL Server Database

Execute the below query to identify the current state of all the SQL Server Database in your Instance.

Use master
GO
SELECT
@@SERVERNAME AS [Server Name],
NAME AS [Database Name],
DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model],
DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
FROM dbo.sysdatabases
ORDER BY NAME ASC
GO

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.