Posted by: codeproject | September 12, 2007

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

Posted in SQL Server 2005

«
»

Responses

  1. Lovely I like it

    By: Vijayan on September 25, 2007
    at 8:02 am

    Reply

  2. tq

    By: thedotnetdudes on December 12, 2007
    at 8:40 am

    Reply

  3. 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

    By: Syed on June 25, 2009
    at 10:54 am

    Reply


Leave a response






Your response:

Categories