SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


master..sysdatabases.status codes


master..sysdatabases.status codes

Author
Message
hot2use
hot2use
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 71

Hiyas

I was just wondering if any of you guys work with the master..sysdatabases.status column? It is normaly altered via the Enterprise Manager, but I came across it because I was checking a database for locked ressources and found this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;165918

I then rummaged around a bit and found the following decimal status codes. These are valid status codes. (I've converted them to binary just for the sake of it.)

0000000000010000 = 16 Online / Normal
0000001000010000 = 528 Offline Mode
1000000000000000 = 32768 Emergency Mode

Does anybody know of any other codes? Or does anybody have a table with the relevant binary values?


Thanks




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

noeld
noeld
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23776 Visits: 2048

From BOL:

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

I know is not complete but MS defined the rest as undocumented




* Noel
hot2use
hot2use
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 71

Dumbass me... Should have known better:

"If you can't find it online, search for it in BOL".

Thanks for pointing me in the right direction again.

Cheers




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

Steve Jones
Steve Jones
SSC Guru
SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)

Group: Administrators
Points: 152832 Visits: 19455
Don't feel too bad. The searching functions in MS aren't the greatest.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Barbara Arnold
Barbara Arnold
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1

Hi,

I have a database (SQL Server 2000) with a sysdatabases.status code of 28. I have looked everywhere in my documentation and on line and cannot find info on this code or find it even listed anywhere. All my other databases have the normal codes listed. This database was recently moved from our "development environment" to our "production environment" by our developers. This really has me scratching my head! Anyone ever hear of a status code of 28?

Thanks,

Barbara


THANG HOANG
THANG HOANG
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 551
The status code contains a decimal representation of the bit mode data.
In your case, value 28 = 4 + 8 + 16
Which means all of the conditions below.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
herman maes
herman maes
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 1

I'm working at some monitoringprocedures (some nagios-server has to monitor a sql server).

I found already some databasestates, but now I have to classify them in OK/warning/critical.

warning= states 4096 - 256 - 1024 - 32768 - 512 - 528??

critical = states 64 - 128 - 2048 - 320 - 1???

case @databasestate
when (1) then convert(char(10), @databasestate) + ': autoclose; set with sp_dboption.'
when (4) then convert(char(10), @databasestate) + ': select into/bulkcopy; set with sp_dboption.'
when (8) then convert(char(10), @databasestate) + ': trunc. log on chkpt; set with sp_dboption.'
when (16) then convert(char(10), @databasestate) + ': torn page detection, set with sp_dboption.'
when (24) then convert(char(10), @databasestate) + ': trunc. log on chkpt + torn page detection'
when (28) then convert(char(10), @databasestate) + ': select into/bulkcopy + trunc. log on chkpt + torn page detection'
when (32) then convert(char(10), @databasestate) + ': loading.'
when (64) then convert(char(10), @databasestate) + ': pre recovery.'
when (128) then convert(char(10), @databasestate) + ': recovering.'
when (256) then convert(char(10), @databasestate) + ': not recovered.'
when (320) then convert(char(10), @databasestate) + ': not recovered. + pre recovery. Suspect'
when (512) then convert(char(10), @databasestate) + ': offline; set with sp_dboption.'
when (528) then convert(char(10), @databasestate) + ': offline + torn page detection'
when (1024) then convert(char(10), @databasestate) + ': read only; set with sp_dboption.'
when (2048) then convert(char(10), @databasestate) + ': dbo use only; set with sp_dboption.'
when (4096) then convert(char(10), @databasestate) + ': single user; set with sp_dboption.'
when (32768) then convert(char(10), @databasestate) + ': emergency mode.'
when (4194304) then convert(char(10), @databasestate) + ': autoshrink.'
when (16777216) then convert(char(10), @databasestate) + ': auto create statistics.'
when (1073741824) then convert(char(10), @databasestate) + ': cleanly shutdown.'
when (1090519040) then convert(char(10), @databasestate) + ': Auto Update Statistics + auto create statistics.'
when (1073741840) then convert(char(10), @databasestate) + ': cleanly shutdown. + torn page detection'


chloe.williams
chloe.williams
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 163
I know this thread is WAY out of date but some of us still work on SQL Server 2000 (not for much longer - new system is currently in build phase thank goodness) but you can check for any value from the status list as shown previously. This one...

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

by using the BITWISE operator.

So, say you are listing databases from the sysdatabases table but you don't want to include databases that are offline or a single user mode you can put a where clause that states;

WHERE
(status & 512) = 0
AND
(status & 4096) = 0

Best demonstrated with some smaller statuses. Assume a database has status 2060 but you don't know if it has torn page detection (status = 16)

You SELECT CASE WHEN (status & 16) = 0 THEN 'NO Torn Page Detection' ELSE 'Torn Page Detection' END

Your bitwise operation is actually this... (2060 & 16)

In the background the two integer values are converted to binary (apologies for the poor formatting!)

decimal
value 16 2060
1 0 0
2 0 0
4 0 1
8 0 1
16 1 0
32 0 0
64 0 0
128 0 0
256 0 0
512 0 0
1024 0 0
2048 0 1
4096 0 0

And then each pair are multiplied together such as
0000000010000
*
0100000001100

basically when you are multiplying two binary numbers together 0*0 = 0, 1*0 = 0, 0*1 = 0, and 1*1 = 1

So we end up with 0000000000000 (no binary column has a 1 in both numbers).

The resulting value of zero means that the status of this database does NOThave Torn Page Detection.

if the status on the other hand had been 2076, then the two binary numbers multiplied would be

0000000010000
*
0100000011100

and the resultant BITWISE operation would have returned a value of 1 (a 1 appears in the 16 column in both binary numbers).

Hope this helps somebody somewhere someday.

Chloe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search