Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

using the following query:

SELECT Name, Status FROM dbo.sysdatabases
WHERE name IN ( 'db_name' )

I get in some cases status = 24, other times 1024 and other times 65544

I see that even when status is 24 I can open and check content of the database from SQL Management Studio but not from a legacy application which checks status to be at least 1024.

when I ask DBAs to fix this issue they run some magic scripts and status goes up to 65544 and all works fine, I would like to have some names for these statuses not just numbers like 24, 1024, 65544.

I have already checked this link: sysdatabases

which contains this table:

Status bits, some of which can be set by the user with ALTER DATABASE (read only, offline, single user, and so on):

1 = autoclose; set with ALTER DATABASE.

4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.

8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.

16 = torn page detection, set with ALTER DATABASE.

32 = loading.

64 = pre recovery.

128 = recovering.

256 = not recovered.

512 = offline; set with ALTER DATABASE.

1024 = read only; set with ALTER DATABASE.

2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.

4096 = single user; set with ALTER DATABASE.

32768 = emergency mode.

4194304 = autoshrink , set with ALTER DATABASE.

1073741824 = cleanly shutdown.

but I don't see 24 and 65544 in there :(

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

The others are combinations of the documented flags, e.g. 24 is 16 and 8.

This is a method for simulating optional parameters and is used in e.g. C iirc, the numbers 1, 2, 4, 8 and so on are structured like that because they correspond to binary values that combined in any unique way create a unique number. The function that accepts them then use bitmasking to extract each individual number. E.g. if you send the value 6 to a function, then we know that this is a combination of 4 and 2, to find this out using a bit mask we would do:

    0110 // 6
AND 0010 // 2
 == 0010

    0110 // 6
AND 0100 // 4
 == 0100

Both statements would be evaluated to true, thereby we have found out the individual values.

Note: In the real world, we would iterate through all values from 1, 2, 4, 8, 16 and so on to the maximum value of an integer (or whatever datatype the parameter was set to).

share|improve this answer
    
correct, edited! :) –  Davide Piras Dec 2 '11 at 11:37
    
@DavidePiras Thanks for commenting about your edit, thought I was imagining things ;). I've edited my answer. –  Andreas Ågren Dec 2 '11 at 11:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.