SQL SERVER 2005 – How to get Database Status

Here are queries to get the database status

SELECT state_desc DatabaseStatus_sysDatabaseFROM sys.databasesWHERE name = ‘DatabaseName’

Another way.

SELECT DATABASEPROPERTYEX(‘DatabaseName’, ‘Status’) DatabaseStatus_DATABASEPROPERTYEX

 Database status result

Advertisements

4 thoughts on “SQL SERVER 2005 – How to get Database Status

  1. Hi

    I’m bit confuse on Recovery_Pending and Suspect Status

    SQL Server 2005 SP3
    I followed this Sceanrio:

    Create a new database suppose name “Test”
    Stop the SQL Server
    rename the existing ../DATA/test.df file to …/DATA/test.sav
    Restart the SQL Server

    And run the following Query
    SELECT DATABASEPROPERTYEX (Test,’status’)
    OUTPUT : SUSPECT

    BUT When I Run:
    select state, state_desc, is_in_standby from sys.databases where database_id = db_id(’Test’)
    OUTPUT
    State 3, State_desc RECOVERY_PENDING, is_in_standy 0

    I found there is descripancy in the result
    State of Suspect is 4 in sys.databases but it shown 3 whixh is REcovery_Pending

    Would you please clarify this.
    Thanks in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s