Methods to determine the status of a SQL Server database


By:   |   Updated: 2008-04-15   |   Comments (3)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


Problem

I think that just about all aspects of the SQL Server Management Studio can be exposed by using T-SQL instead of having to use the GUI.  I have been trying to write some routines to determine whether a database is online as well as to check some other information about the status of a database.  What options are there to get status information for a database without using the GUI?

Solution

There 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 * FROM sys.databases 

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.

database name

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.

messages

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.

Next Steps
  • Now that you have a few different ways of finding the status of a database, determine which method works best for you situation
  • Example 1 shows the best method to use for SQL 2005 and future versions, although example 3 works just fine, you may want to use the sys.databases system view


Last Updated: 2008-04-15


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Comments For This Article




Thursday, April 26, 2012 - 11:46:57 AM - neoeast Back To Top (17133)

How about  below query if we just need status of all database on the server :

 

SELECT name as Database_Name, state_desc as Database_Status

FROM sys.databases


Friday, April 18, 2008 - 2:47:50 PM - admin Back To Top (891)

Thanks for the update.  We will make sure the tip is amended.


Tuesday, April 15, 2008 - 10:53:08 AM - tosscrosby Back To Top (872)

Great topic. Here's my list that I picked up from somwhere (for SQL2K). It answers a couple of your "not sure" values. HTH.

 

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



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools