SQL Server database states

Because it is necessary to monitor the states of databases in SQL Server, one way of ding this is by executing following SQL command:

select count(*) as NoOnline from master.sys.databases where state <> 0

This script will return the number of databases in SQL Server which are not online.

If we would like to see what is the current state of specific database we can do it with following SQL:

select state_desc from master.sys.databases where name = 'database_name'

Field state_desc can have following database statuses values:

ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

Description of these states can be found on following link.