where is the database 'state' is stored

  • where is the database 'state' is stored.

    Means in which table or view.

  • have you checked sysdatabases? i don't have a sql 7 instance available. try sysdatabases or sys.databases

  • yes i checked .there is no 'state column in sysdatabases.'

  • It looks like it is the status column you are after but it is the addition of the bits. Someone has probably written a script to query this but here are the definition from BOL.

    Status bits, some of which can be set by using ALTER DATABASE as noted:

    1 = autoclose (ALTER DATABASE)

    4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

    8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

    16 = torn page detection (ALTER DATABASE)

    32 = loading

    64 = pre recovery

    128 = recovering

    256 = not recovered

    512 = offline (ALTER DATABASE)

    1024 = read only (ALTER DATABASE)

    2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

    4096 = single user (ALTER DATABASE)

    32768 = emergency mode

    4194304 = autoshrink (ALTER DATABASE)

    1073741824 = cleanly shutdown

    Multiple bits can be ON at the same time.

  • Found this on the site:

    select name as DBNAME,

    CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],

    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_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],

    CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],

    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_(ALTER_DATABASE)],

    CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]

    from master.dbo.sysdatabases

  • i want to know the 'state' of the databases.

    Means online,offline,suspect.

    where this information stored?

  • ok, what about the status column of the sp_helpdb sproc?

    edit: doesn't look like this returns anything for offline databases in sql 2008

  • Mat gave you the script to read the flags of the status column; that is exact where it is stored/accessible...it might not be exactly what you wanted, but that's where it is.

    here's the both status and status2 for every db;

    In 2005, the info is a little easier to read in sys.databases (select * from sys.databases)

    select name as DBNAME,STATUS,

    CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],

    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_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],

    CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],

    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_(ALTER_DATABASE)],

    CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]

    from master.dbo.sysdatabases

    SELECT name as DBNAME,STATUS2,

    CASE WHEN (STATUS2 & 16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled_(set_by_using_sp_fulltext_database)]

    from master.dbo.sysdatabases

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply