Microsoft.SqlServer.Management.Smo.DatabaseStatus values different from sysdatabases status column values

  • Not sure if anyone noticed this. Looks like the status bits defined in SMO object is not actually same as that stored in status column of sysdatabases. As I am using SMO i am not able to exactly figure out the status of the database when status goes to offline. I know that I can use DATABASEPROPERTYEX which gives the status in string but I would like to get the status bit value.

    Kiran

    Status bits defined in sysdatabases

    status int

    Status bits, some of which can be set by using ALTER DATABASE as noted:

    1 = autoclose (ALTER DATABASE)

    4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

    8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

    16 = torn page detection (ALTER DATABASE)

    32 = loading

    64 = pre recovery

    128 = recovering

    256 = not recovered

    512 = offline (ALTER DATABASE)

    1024 = read only (ALTER DATABASE)

    2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

    4096 = single user (ALTER DATABASE)

    32768 = emergency mode

    4194304 = autoshrink (ALTER DATABASE)

    1073741824 = cleanly shutdown

    Multiple bits can be ON at the same time

    Status bit defined in SMO.

    namespace Microsoft.SqlServer.Management.Smo

    {

    // Summary:

    // The Microsoft.SqlServer.Management.Smo.DatabaseStatus enumeration is a list

    // of constant values that specify the status of a database, whether it be loading,

    // recovering, or normal, for example. This enumeration has a FlagsAttribute

    // attribute that allows a bitwise combination of its member values.

    [Flags]

    public enum DatabaseStatus

    {

    // Summary:

    // The database is available.

    Normal = 1,

    //

    // Summary:

    // The database is going through the restore process.

    Restoring = 2,

    //

    // Summary:

    // The database is waiting to go through the recovery process.

    RecoveryPending = 4,

    //

    // Summary:

    // The database is going through the recovery process.

    Recovering = 8,

    //

    // Summary:

    // The database has been marked as suspect. You will have to check the data,

    // and the database might have to be restored from a backup.

    Suspect = 16,

    //

    // Summary:

    // The database has been taken offline.

    Offline = 32,

    //

    // Summary:

    // The database is inaccessible. The server might be switched off or the network

    // connection has been interrupted.

    Inaccessible = 62,

    //

    // Summary:

    // The database is in standby mode.

    Standby = 64,

    //

    // Summary:

    // The server on which the database resides has been shut down.

    Shutdown = 128,

    //

    // Summary:

    // The database is in emergency mode.

    EmergencyMode = 256,

    //

    // Summary:

    // The database has been automatically closed.

    AutoClosed = 512,

    }

    }

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply