Feature Request: increase the number of entries in spt_values to reflect the documented sysdatabases.status(1-3) bitmaps

The sysdatabases.status(1-3) bitmaps are listed in Sybooks.

1> select type,
2>      name,
3>      number,
4>      inttohex(number)
5>   from spt_values
6>   where spt_values.type in ("D", "D2", "D3", "D4")
7>   group by type order by type, number asc
8> go
 type name                         number
 —- —————————- ———– ——–
 D    DATABASE STATUS                       -1 FFFFFFFF
 D    failed upgrade                         1 00000001
 D    select into/bulkcopy/pllsort           4 00000004
 D    trunc log on chkpt                     8 00000008
 D    trunc. log on chkpt.                   8 00000008
 D    no chkpt on recovery                  16 00000010
 D    dont recover                         32 00000020
 D    not recovered                        256 00000100
 D    ddl in tran                          512 00000200
 D    read only                           1024 00000400
 D    dbo use only                        2048 00000800
 D    single user                         4096 00001000
 D    allow nulls by default              8192 00002000
 D    ALL SETTABLE OPTIONS               15900 00003E1C
 D    inconsistent security label        16384 00004000

 D2   abort tran on log full                 1 00000001
 D2   no free space acctg                    2 00000002
 D2   auto identity                          4 00000004
 D2   identity in nonunique index            8 00000008
 D2   offline                               16 00000010
 D2   unique auto_identity index            64 00000040
 D2   ALL SETTABLE OPTIONS                  79 0000004F
 D2   has suspect pages/objects            128 00000080
 D2   online for standby access           1024 00000400
 D2   mixed log and data                 32768 00008000

 D3   quiesce database                     128 00000080
 D3   user created temp db                 256 00000100
 D3   async log service                   1024 00000400
 D3   delayed commit                      2048 00000800
 D3   archive database                 4194304 00400000
 D3   compressed data                  8388608 00800000
 D3   scratch database                16777216 01000000
 D3   ALL SETTABLE OPTIONS            16780288 01000C00
 D3   compressed log                 134217728 08000000

 D4   ALL SETTABLE OPTIONS                   0 00000000

The benefit of putting the entries into spt_values is: Developers and DBAs are able to programmatically determine the current status of the databases. Currently we make our own bitmask lookup tables which can be error prone to typos and the like.

Example bitmask lookup table:

# sysdatabases.status2
my %status2 = (
    S2_ABORT_TRAN_LOGFULL                       => 0×0001,
    S2_NO_FREESPACE_ACTG                        => 0×0002,
    S2_AUTO_IDENTITY                            => 0×0004,
    S2_IDENITY_NONUNIQUE_INDEX                  => 0×0008,
    S2_OFFLINE                                  => 0×0010,
    S2_OFFLINE_FOR_RECOVERY                     => 0×0020,
    S2_AUTOIDENTITY_UNIQUE_CONSTRAINT           => 0×0040,
    S2_SUSPECT_PAGES                            => 0×0080,
    S2_TABLE_STRUCTURE_WRITTEN_DISK_RECOVERY    => 0×0100,
    S2_BEING_UPGRADED                           => 0×0200,
    S2_ONLINE_STANDBY                           => 0×0400,
    S2_NO_CROSS_DB_ALIAS_ACCESS                 => 0×0800,
    S2_LOG_ON_NONLOG_DEVICE                     => 0xFFFF8000
);

The missing documented entries from spt_values are:

sysdatabases.status column:
0×02 – upgrade successful
0×64 – recovery started for all databases to be recovered

sysdatabases.status2:
0×0100 – table structure written to disk ….
0×0200 – database in the process of being upgraded
0×0800 – cross database access via an alias mechanism disabled

sysdatabases.status3:
0×0001 – db is user created proxy db
0×0002 – db is proxy db created by HA
0×0004 – db has a proxy db created by HA
0×0008 – disallow access to db – db being shutdown
0×0010 – db is failed over db
0×0020 – db is mounted db of type master
0×0040 – db is mounted db
0×0200 – disallow external access to db in the server in failed over state
0×1000 – db has been shutdown successfully
0×2000 – drop db in progress

Update: Sybase has created CR 481906 for this :)

« »