Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

master..sysdatabases.status codes Expand / Collapse
Author
Message
Posted Monday, December 13, 2004 5:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #150618
Posted Monday, December 13, 2004 8:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028

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
Post #150702
Posted Monday, December 13, 2004 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #150714
Posted Monday, December 13, 2004 10:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:00 PM
Points: 33,204, Visits: 15,354
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
Post #150727
Posted Wednesday, February 2, 2005 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 28, 2005 6:32 AM
Points: 1, 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

Post #159111
Posted Wednesday, February 2, 2005 4:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:25 PM
Points: 25, Visits: 514
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.
Post #159216
Posted Wednesday, August 10, 2005 3:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 7, 2005 4:28 AM
Points: 164, 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'

Post #208994
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse