Check the Status of a SQL Server database

Methods to determine the status of a SQL Server database

SolutionThere are several different ways that this can be accomplished and a lot of it depends on what information you are trying to return.  The following examples show a few ways to determine the status of a database.

---

Example 1 - sys.databases catalog view
 
SELECT user_access_desc,recovery_model_desc,log_reuse_wait_desc, * FROM sys.databases 

(i.e)

SELECT * FROM sys.databases

To get a better idea of what this catalog view is comprised of take a look at this documentation.
The following is the output for the master database.  The result set has been transposed for easier viewing.
In most cases a value of 0 = off and a value of 1 = on. For a complete listing of these columns and what the values mean refer to this article. 

ColumnName	Value
name	master
database_id	1
source_database_id	NULL
owner_sid	0x01
create_date	4/8/03 9:13
compatibility_level	90
collation_name	SQL_Latin1_General_CP1_CI_AS
user_access	0
user_access_desc	MULTI_USER
is_read_only	0
is_auto_close_on	0
is_auto_shrink_on	0
state	0
state_desc	ONLINE
is_in_standby	0
is_cleanly_shutdown	0
is_supplemental_logging_enabled	0
snapshot_isolation_state	1
snapshot_isolation_state_desc	ON
is_read_committed_snapshot_on	0
recovery_model	3
recovery_model_desc	SIMPLE
page_verify_option	2
page_verify_option_desc	CHECKSUM
is_auto_create_stats_on	1
is_auto_update_stats_on	1
is_auto_update_stats_async_on	0
is_ansi_null_default_on	0
is_ansi_nulls_on	0
is_ansi_padding_on	0
is_ansi_warnings_on	0
is_arithabort_on	0
is_concat_null_yields_null_on	0
is_numeric_roundabort_on	0
is_quoted_identifier_on	0
is_recursive_triggers_on	0
is_cursor_close_on_commit_on	0
is_local_cursor_default	0
is_fulltext_enabled	0
is_trustworthy_on	0
is_db_chaining_on	1
is_parameterization_forced	0
is_master_key_encrypted_by_server	0
is_published	0
is_subscribed	0
is_merge_published	0
is_distributor	0
is_sync_with_backup	0
service_broker_guid	00000000-0000-0000-0000-000000000000
is_broker_enabled	0
log_reuse_wait	0
log_reuse_wait_desc	NOTHING
is_date_correlation_on	0

---

Example 2: DATABASEPROPERTYX function

SELECT DB_NAME() AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus

The DATABASEPROPERTYX function only allows you to see one element at a time, but this maybe helpful if that is all you need.
Here we can see the Status for the master database by issuing the above query.

See this previous tip for additional information about DATABASEPROPERTYX

---

Example 3: Status column from sys.sysdatabases
Another approach is to use the status column from the sys.sysdatabases system view.  This view has been maintained in SQL Server 2005 for backwards compatibility, so example 1 should be the preferred method, but this approach is still valid.

DECLARE @status INT SELECT @status = status FROM sys.sysdatabases WHERE name = DB_NAME() PRINT DB_NAME() + ' - ' + CONVERT(VARCHAR(20),@status) IF ( (1 & @status) = 1 ) PRINT 'autoclose' IF ( (2 & @status) = 2 ) PRINT '2 not sure' IF ( (4 & @status) = 4 ) PRINT 'select into/bulkcopy' IF ( (8 & @status) = 8 ) PRINT 'trunc. log on chkpt' IF ( (16 & @status) = 16 ) PRINT 'torn page detection' IF ( (32 & @status) = 32 ) PRINT 'loading' IF ( (64 & @status) = 64 ) PRINT 'pre recovery' IF ( (128 & @status) = 128 ) PRINT 'recovering' IF ( (256 & @status) = 256 ) PRINT 'not recovered' IF ( (512 & @status) = 512 ) PRINT 'offline' IF ( (1024 & @status) = 1024 ) PRINT 'read only' IF ( (2048 & @status) = 2048 ) PRINT 'dbo use only' IF ( (4096 & @status) = 4096 ) PRINT 'single user' IF ( (8192 & @status) = 8192 ) PRINT '8192 not sure' IF ( (16384 & @status) = 16384 ) PRINT '16384 not sure' IF ( (32768 & @status) = 32768 ) PRINT 'emergency mode' IF ( (65536 & @status) = 65536 ) PRINT 'online' IF ( (131072 & @status) = 131072 ) PRINT '131072 not sure' IF ( (262144 & @status) = 262144 ) PRINT '262144 not sure' IF ( (524288 & @status) = 524288 ) PRINT '524288 not sure' IF ( (1048576 & @status) = 1048576 ) PRINT '1048576 not sure' IF ( (2097152 & @status) = 2097152 ) PRINT '2097152 not sure' IF ( (4194304 & @status) = 4194304 ) PRINT 'autoshrink' IF ( (1073741824 & @status) = 1073741824 ) PRINT 'cleanly shutdown'

Below is the output for the master database.

This is just a simple way of looking at this data using the PRINT statement.  This could be taken a step further where the data is concatenated or you could do the test for just one portion of the value.

or


script:

select name as DBNAME,getdate() as RUNDATE, STATUS, STATUS2,
CASE WHEN (STATUS &         1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE],
CASE WHEN (STATUS &         4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO_BULKCOPY],
CASE WHEN (STATUS &         8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC_LOG_ON_CHKPT],
CASE WHEN (STATUS &        16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION],
CASE WHEN (STATUS &        32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS &        64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS &       128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS &       256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS &       512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE],
CASE WHEN (STATUS &      1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY],
CASE WHEN (STATUS &      2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY],
CASE WHEN (STATUS &      4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER],
CASE WHEN (STATUS &     32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS &   4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN],
CASE WHEN (STATUS2 &     16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default],  --This one
CASE WHEN (STATUS2 &     65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null],
CASE WHEN (STATUS2 &    131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers],  -- This one
CASE WHEN (STATUS2 &   1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor], --This one
CASE WHEN (STATUS2 &   8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier],
CASE WHEN (STATUS2 &  33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit],
CASE WHEN (STATUS2 &  67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls],
CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings],
CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled]
from master.dbo.sysdatabases