sysdatabases status 536 instead of 512

Question

Post reply

sysdatabases status 536 instead of 512

AlexSQLForums

SSChampion

Points: 14277
More actions
June 5, 2007 at 8:47 am

#62511

Hi all
I have a script which updates stats on all databases.
I exclude system and offline databases by selecting name from sysdatabases where status <> 512 and dbid not in(1,2,3,4).
However on one of my servers a 50GB and a 100MB databases are offline but in status it says 536 and 528.
Does anyone know what 536 and 528 stands for???
How can i avoid all offline databases.
Thank you

Alex S

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Could you think in the way?

528 = 512 (Offline) + 16 (Torn Page Detection)

536 = 512 (Offline) + 16 (Torn Page Detection) + 8 (Trunc. Log on Chkpt)

To avoid all offline,

status < 512 and status > 1023

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

I believe the database with status 536 has the following characteristics:

offline, Truncate Log on Checkpoint 'on', Torn Page Detection 'on'

and the database with 528 has these:

offline, Torn Page Detection 'on'

This query helps to interpret the status code:

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 got it from this thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=326235

Greg

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

Thanks for your replies

I will use

select name from master..sysdatabases where dbid not in(1,2,3,4) and status not in(512,528,536)

or i can use

select name from master..sysdatabases where dbid not in(1,2,3,4) and version is not null

It looks like version is also null for all offline databases.

Alex S

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

Your script may be ok to your current situation, but it not enough to include all other situations. You need to use status > 511 and status <1024 instead instead if you would like to include all offline databases.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

SQL ORACLE it doesn't work

select name from master..sysdatabases where dbid not in(1,2,3,4) and status > 511 and status <1024.

so will use this query from now on.

select name from master..sysdatabases where dbid not in(1,2,3,4) and status not in(512,528,536)

Thank you

Alex S

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Or you can do this in the where clause:

(status & 512) = 512

This will be true for any status value where the database is offline without having to figure out the different combinations that may occur.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Another way to skin the cat. This onea bit more portable between versions:

select name from sysdatabases

where name not in ('master','model','msdb','tempdb','pubs','Northwind')

and DATABASEPROPERTYEX(name,'status')='ONLINE'

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Forgot about DATABSEPROPERTYEX, another good catch.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 9

THANK YOU ALL FOR ALL YOUR REPLIES.

RUDY i modified my existing statement and added DATABASEPROPERTYEX and also used it to avoid read only databases as well.

select name from sysdatabases

where name not in ('master','model','msdb','tempdb','pubs','Northwind')

and DATABASEPROPERTYEX(name,'status')='ONLINE' and DATABASEPROPERTYEX(name,'updateability')<>'READ_ONLY'

Alex S

Ben Amor Newbie Points: 5 More actions · Answer 10

Great tip, good on ya. Didn't realise that this field was a bitmask.

Is the full range of values for this field documented in (eg) BOL?