tsql to find status of a database in words

Question

I want to know status of every database across a SQL Server farm. I was using:

select name,
       case status 
         when 32 then 'loading'
         when 128 then 'recovering'
         when 512 then 'offline'
         when 4096 then 'single user'
         when 64 then 'pre recovery'
         when 256 then 'not recovered'
         else 'Normal'
       end  
 from sysdatabases
where name not in('master','msdb','model','tempdb','reportserver','reportservertempdb','datacomsqlaudit','pubs','distribution','northwind')

But a friend told me that Status could be combination of 2 e.g. 32+128 = 32128. How can I find database status using this figure?

Answer 1

The status number is made up of the "on" bits of the status field value. So, status can be a combination of 2 states (eg 32 + 128) but you can get the individual states by checking the values of specific bits in the status value.

You could do something like this:

SELECT 
CASE (status & 32) WHEN 32 THEN 'loading' ELSE '' END + ' ' +
CASE (status & 128) WHEN 128 THEN 'recovering' ELSE '' END as status
FROM sysdatabases
WHERE NAME NOT IN ('master','msdb','model','tempdb','reportserver','reportservertempdb','datacomsqlaudit','pubs','distribution','northwind')

See different status bits of sysdatabases in sqlserver for an example script that checks for specific values.

EDIT: MSDN library has this to say about the values of the various bits in the status and status2 fields of sysdatabases

Answer 2

All of the status numbers are shown in base 10 (decimal, our usual numbering system). However, you will note that all of the numbers are a multiple of 2 because they represent a bit position (base 2, 0 or 1).

512 decimal = 200 hex = 0010 0000 0000 binary

1024 decimal = 400 hex = 0100 0000 0000 binary

The & is the bitwise AND operator. The logic table is:

A   B    A AND B
0   0       0
1   0       0
0   1       0
1   1       1

As you can see, if you AND two bits together, both bits must be 1 for the result to be 1. The AND ( & ) operator is used to mask all of the other bits to determine whether or not a particular bit is set.

So, if you AND the status value with 512, then result will be 512 if the bit is set. Otherwise, it will be zero.

Since 512 is the 10th bit (counting right to left), status & 512 will AND all of the bits in the status value with 0100 0000 0000. If the 10th bit is a 1 in the status value, the result will be 1, indicating that the OFFLINE option is turned on (set).

To use the status columns effectively, you need at a least a rudimentary knowledge of binary and hexidecimal number systems. Actually, the same principles apply of any base (you just a way to represent each digit for large bases).

Manjot
This will get you started:

1. You need to create a sproc that will convert varbinary into a hexstring - see Microsoft's INFO: Converting Binary Data into Hexadecimal String

2. The following sql:

   SELECT sus.status,
          sus.stat,
          CASE WHEN PATINDEX('%8', sus.stat) > 0 THEN 1 ELSE NULL END 'trunc. log on chkpt; set with sp_dboption.',
          CASE WHEN PATINDEX('%1%', sus.stat) = 9 THEN 1 ELSE NULL END 'torn page detection, set with sp_dboption.',
          CASE WHEN PATINDEX('%1%', sus.stat) = 8 THEN 1 ELSE NULL END 'loading.',
          CASE WHEN PATINDEX('%1%', sus.stat) = 7 THEN 1 ELSE NULL END 'pre recovery.'
     FROM (SELECT t.status,
                  sp_hexadecimal(CONVERT(varbinary(8), t.status)) 'stat'
             FROM SYSDATABASES t) sus