Wednesday, 12 December 2012

Different Database States in SQL Server 2012



Database can always be in any one of the specific state. 

To identify the current database state, this script can be used.

>> SELECT state_desc  from SYS.DATABASES where name = 'EmployeeDB'

-- ONLINE

Database States can be categorized into 

1.   ONLINE

Ø  The primary file group status is online. It is available for access to users.

2.   OFFLINE

Ø  Database is not available for user access. Once Database becomes offline state, it requires user action to bring it online back.
Ø  Usually while we are moving database files, we make database state to offline and move the database files to specific location and change the database state to online.

3.   RESTORING

Ø  Database is not available for user access.
Ø  In this stage, one or more primary file group files are in the process of restoring.

4.   RECOVERING

Ø  Database is not available for user access.
Ø  It is in the process of database recovery state [Transient State].
Ø  If recovery is successful, database will automatically come to online state. If recovery is not successful, then database will go to suspect mode.

5.   RECOVERY PENDING

Ø  Database is not available for user access.
Ø  It means, database is not able to come to online state due to some resource related error which means database is not damaged but one or more files are missing or Resource limitation of system is preventing database to start etc..
Ø  It requires user attention to resolve the issue and let the database to complete the recovery process.

6.    SUSPECT

Ø  Database is not available for user access.
Ø  It means, database recovery process is unsuccessful. It may be because of primary file group is damaged.
Ø  Database cannot be recovered during the start-up of SQL Server. It requires user action to resolve the issue.

7.   EMERGENCY

Ø  Database is available for READ_ONLY to sysadmin user.
Ø  It is mainly used for troubleshooting purpose.
Ø  If database is in suspect mode, it cannot be accessible to anyone. In this case sysadmin can change its state from suspect to EMERGENCY. So that sysadmin will get READ_ONLY access to database to troubleshoot and fix the issue.

Query to make database ONLINE\OFFLINE

Below queries can be used to make database ONLINE\OFFLINE.

>> USE [master]

>> ALTER DATABASE <<DB Name>> SET OFFLINE WITH ROLLBACK IMMEDIATE


>> ALTER DATABASE <<DB Name>> SET ONLINE