Is there a good way to understand the sysdatabases STATUS fields?

  • For my own benifit I am attempting to understand how to correlate what I see in the status field in sysdatabases to what is shown in BOL for sysdatabases and I am lost.

     

    for instance...I have one database that is part of a log shipping process.  The database is in a READ-ONLY\Offline status

    when queried in QA I find the status to be 6292480

    in BOL I see that it says a db in READ-ONLY\OFFLINE has a status of

    512 = offline; set with sp_dboption.

    1024 = read only; set with sp_dboption.

    Also BOL said

    Multiple bits can be on at the same time.

     

    OK, how do you get that big number 6292480 out of 512 and 1024???

     

    What does that mean?  I guess I need a way to understand how to approach the status numbers and how to break them down into a number or status that actually means something

    If anyone can comment on a good way to figure out the statuses I would appreciate it...as I am not finding much via google or BOL...

     

    Thanks,

    leeland

  • From the MSDN site:

    status

    int

    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.

    status2

    int

    16384 = ANSI null default (ALTER DATABASE)

    65536 = concat null yields null (ALTER DATABASE)

    131072 = recursive triggers (ALTER DATABASE)

    1048576 = default to local cursor (ALTER DATABASE)

    8388608 = quoted identifier (ALTER DATABASE)

    33554432 = cursor close on commit (ALTER DATABASE)

    67108864 = ANSI nulls (ALTER DATABASE)

    268435456 = ANSI warnings (ALTER DATABASE)

    536870912 = full text enabled (set by using sp_fulltext_database)


    And then again, I might be wrong ...
    David Webb

  • I saw that on the website but what I am asking is how do you convert what is in the status field to an actual status?  I mean the number doesn't correlate to anything that I can make sense of at the moment.

     

     

    For instance...the status of the database I am speaking about is listed as 6292480 but in BOL or MSDN it doesn't list that number at all.  Do you have to convert the number some how?

  • expanding on what David Web Posted, There's a SQL at the to help interpret what is on/off.

    typical results:

    DBNAMEAUTOCLOSE_(ALTER_DATABASE)SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)TORN_PAGE_DETECTION_(ALTER_DATABASE)LOADINGPRE_RECOVERYRECOVERINGNOT_RECOVEREDOFFLINE_(ALTER_DATABASE)READ_ONLY_(ALTER_DATABASE)DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)SINGLE_USER_(ALTER_DATABASE)EMERGENCY_MODEAUTOSHRINK_(ALTER_DATABASE)CLEANLY_SHUTDOWN
    PRODUCTIONFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSE
    masterFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
    modelFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUE
    msdbFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
    NorthwindFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
    PERFECTFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSE
    pubsFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
    tempdbFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE

    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

    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!

  • As I see above, they're bit maps. So if you set the 4th bit, leaving, 1, 2, 3 at zero, you have 1000 in binary, or 8.

    Since you're dealing with a 32-bit number, the larger numbers mean the larger bits are set.

  • while I had the macro going for changing the data to a sql, here's the way to interpret STATUS2:

    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!

  • Hi Lowell,

     

    Thanks for the post, your code works great, could you please explain to me what the (STATUS & 512) does and how it works...

     

    For instance I made a test DB, and put it in READ-ONLY mode, when I queried the sysdatabases table it showed a status for the new DB of 1032

    I then went and put the test DB in READ-ONLY and OFFLINE which resulted in a status of 1544.

    SO when you run your query, and you have a status of 1544, what does the statement of (STATUS & 512) mean?  what does the & do in that statement.  Because when I put that statement to a variable in the query it just results in 512.

    Sorry for probably asking silly questions...the whole concept doesn't match up for me and I have not a clue as to how to correlate the results to what I am actually seeing in the status column for each database.

    Thanks,

    Leeland

  • Check out the "&" section in the books online.  If you need more information search for bitwise operations.

  • Leeland,

    All of the status numbers are shown in base 10 (decimal, our usual numbering system).

    However, you will note that all of the numbers are a multiple of 2 because

    they represent a bit position (base 2, 0 or 1).

    512 decimal = 200 hex = 0010 0000 0000 binary

    1024 decimal = 400 hex = 0100 0000 0000 binary

    The & is the bitwise AND operator. The logic table is:

    A   B    A AND B

    0   0       0

    1   0       0

    0   1       0

    1   1       1

    As you can see, if you AND two bits together, both bits must be 1 for the

    result to be 1. The AND ( & ) operator is used to mask all of the other bits to

    determine whether or not a particular bit is set.

    So, if you AND the status value with 512, then result will be 512 if the bit is

    set. Otherwise, it will be zero.

    Since 512 is the 10th bit (counting right to left), status & 512 will AND all of

    the bits in the status value with 0100 0000 0000. If the 10th bit is a 1 in the

    status value, the result will be 1, indicating that the OFFLINE option is turned

    on (set).

    To use the status columns effectively, you need at a least a rudimentary

    knowledge of binary and hexidecimal number systems. Actually, the same

    principles apply of any base (you just a way to represent each digit for large

    bases).

     

  • This is EXACTLY what I needed as an explination...I see how it releates now and what I need to study to make the connections.  I haven't used binary for a long time, so I will revisit that to better understand the concept.

     

    Thanks Mkeast for the time to write that out...it makes a lot more sense now.

     

    leeland

  • you'll notice that this is really a way to store multiple values in a single data field....it kind of goes against the grain of a relational database...one field=one value and all that, but logic like this is used in a lot of applications;

    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!

  • The explanation in books online wasn't clear enough, are you sure you read it??

  • There are some databases showing as offline. But their status in sysdatabases are 4194840 and 528 (some are 4194840 and the others are 528)...

    Do anyone knows what are those and what are the different?

    Thanks in advance

  • 4194840 = TORN_PAGE_DETECTION,OFFLINE,AUTOSHRINK

    528 = TORN_PAGE_DETECTION,OFFLINE

    my script from page one helps find the values, here's a snippet where i just plug in specific values:

    declare @status int

    SET @status = 528

    select @status as OriginalStatus,

    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]

    eason36 (10/6/2008)


    There are some databases showing as offline. But their status in sysdatabases are 4194840 and 528 (some are 4194840 and the others are 528)...

    Do anyone knows what are those and what are the different?

    Thanks in advance

    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!

  • Thanks. You are genius.

Viewing 15 posts - 1 through 15 (of 17 total)

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