master..sysdatabases.status codes
-
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. -
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 -
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. -
Don't feel too bad. The searching functions in MS aren't the greatest.
-
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
-
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.
-
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'
-
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply