Determining Database status

• brendanc

  Old Hand

  Points: 376

  More actions

  September 10, 2008 at 1:47 am

  #123510

  Hi

  I would like to know how to determine what the status values mean in the status column of sysdatabases. The help files give you a broad definition on a couple of them but not every possible value. Now its also says that the bits can be set in different values. So is there some documented method of calculating what the various settings mean. For instance I had article posted to me from MSSQLtips.com about changing the status of a database if it showed suspect\offline to status 24. But nowhere can I find what does status 24 mean. The help files don't give you all the required info or how to go about doing the calculations.

  And I have databases that are set to 0, 20 and two offline database that have different statuses...

  So if anyone can point me in the right directions

  Thanks

  Brendan

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  September 22, 2008 at 8:29 pm

  #875380

  The bits of status require a knowledge of "bit mapping".

  As you know, the "value" of each bit in a byte or word doubles the previous one. To count the value in bits, bit 0 has a value of "1", bit 1 has a value of "2", bit 2 has a value of "4", bit 3 has a value of "8", and bit 4 has a value of "16". Notice that the bit # indicates what the power of 2 the bit represents. For example, bit 3 has a value of "8" because 2³ = 8.

  So, let's look at the value of 24... that's in decimal. Let's look at that value in binary or bits... in the following, which values can be added together to come up with the number "24"?

  [font="Courier New"]BIT# 5 4 3 2 1 0

  Value 32 16 8 4 2 1[/font]

  That's right... only the numbers 8 and 16 will make the number 24. That means that the database has two statuses... 8 and 16 which, according to sysDatabases in Books Online, means the database has the following 2 options set...

  [font="Courier New"]8 = trunc. log on chkpt; set with sp_dboption.

  16 = torn page detection, set with sp_dboption.[/font]

  Now, if I want do know if the number 24 has the number 16 in it, you have to do a "Bitwise And". Lookup "Bitwise AND" in the index of Books Online for a bit more detail... but here's an answer to "does 24 contain a 16?" In fact, it'll identify all the bits set by their decimal value which is what you need to do to find out the actual status of the database because all of the statuses are decimal bit values...

  DECLARE @status INT

  SET @status = 24

  SELECT @status & 32 AS Bit5,

  @status & 16 AS Bit4,

  @status & 8 AS Bit3,

  @status & 4 AS Bit2,

  @status & 2 AS Bit1,

  @status & 1 AS Bit0

  Let us know if you have any additional questions on this...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)