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

Question

Post reply

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

Leeland

SSCrazy Eights

Points: 8959
More actions
November 28, 2006 at 2:48 pm

#61731

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

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

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

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

Leeland SSCrazy Eights Points: 8959 More actions · Answer 2

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?

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

typical results:

DBNAME

AUTOCLOSE_(ALTER_DATABASE)

SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)

TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)

TORN_PAGE_DETECTION_(ALTER_DATABASE)

LOADING

PRE_RECOVERY

RECOVERING

NOT_RECOVERED

OFFLINE_(ALTER_DATABASE)

READ_ONLY_(ALTER_DATABASE)

DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)

SINGLE_USER_(ALTER_DATABASE)

EMERGENCY_MODE

AUTOSHRINK_(ALTER_DATABASE)

CLEANLY_SHUTDOWN

PRODUCTION

FALSE

TRUE

FALSE

TRUE

FALSE

master

FALSE

TRUE

FALSE

model

FALSE

TRUE

FALSE

TRUE

msdb

FALSE

TRUE

FALSE

Northwind

FALSE

TRUE

FALSE

PERFECT

FALSE

TRUE

FALSE

TRUE

FALSE

pubs

FALSE

TRUE

FALSE

tempdb

FALSE

TRUE

FALSE

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!

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

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.

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

Leeland SSCrazy Eights Points: 8959 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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

vadba SSChampion Points: 11133 More actions · Answer 8

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

Leeland SSCrazy Eights Points: 8959 More actions · Answer 9

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

Lowell SSC Guru Points: 323518 More actions · Answer 10

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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

eason36 SSC Enthusiast Points: 185 More actions · Answer 12

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 SSC Guru Points: 323518 More actions · Answer 13

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!

eason36 SSC Enthusiast Points: 185 More actions · Answer 14

eason36

SSC Enthusiast

Points: 185

October 6, 2008 at 10:15 pm

#881426

Thanks. You are genius.