By: Greg Robidoux | Comments (4) | Related: > Monitoring
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.
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.
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
