What are the names of SQL Server database statuses 24, 1024 and 65544?

Question

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 :(

Answer 1

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).

Answer 2

It is a bitmask:

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

(source: Jens K. Suessmeyer, MSDN forums)