SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.

Following are main database status: (Reference: BOL Database Status)

Database is available for access.

Database is unavailable.

One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.

Database is being recovered.

SQL Server has encountered a resource-related error during recovery.

At least the primary filegroup is suspect and may be damaged.

User has changed the database and set the status to EMERGENCY.

Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.

1) Using T-SQL (My Recommendation)

SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks'



2) Using SQL Server Management Studio

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL Database Status, BOL sys.databases, BOL DATABASEPROPERTYEX

About these ads

28 thoughts on “SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

  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 exixting ../DATA/test.df file to …/DATA/test.sav
    Again Restart the SQL Server

    And runthe following Query

    BUT When I Run:
    select state, state_desc, is_in_standby from sys.databases where database_id = db_id(‘Test’)
    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

    Would you please clarify this.
    Thanks in advance

  2. does this work for linkservers? I’m getting NULL values on some status. but when I run it locally, it only produces ONLINE database status. Please advice. Thanks

  3. Dear Sir,

    We are using Microsoft Dynamics with SQL server 2005
    today I stop all new connection request and transaction from Axapta and start
    shrinking log file through below mentioned command
    (LOG FILE NAME, 50) where the actual size is 67GB.
    simultaneously I am getting the error in AXAPTA that

    ” sql error description: [Microsoft][ODBC SQL Server driver][SQL SERVER] The transaction log for database “AxDynamics” is full. to find out why space in th elog cannot be reuse, see the log_reuse_wait_desc coloum in sys.databases.

    I was too much afraid. that what happens.

    but however what I do that cahnge the log file location and delete the existing one.
    after that problem solved.

    and secondly now our database size is near to 80 GB and tooooooooooooo much slow response when query for any report.

    kindly refer my any third party software and guide me about the sys.database table[that where is its location and how to solve out these type of issues.]

  4. how do i reset the db status from suspect to emrgency (32768)

    where do i need to change the state to 32768, i mean which table,quite dificult find the table…

  5. Is there anyway to find how much restoration is pending for a database in Restoring Mode?

    This information helps for projecting the time for completion.

    Thanks in Advance,
    Pavan Nayakanti

  6. hi Dave ,

    i have logshipping database which around 700gb . recently logshipping job was failed due to insufficient space then i made some space for log file and then restarted the job but it failed . then i thought it might require restart and restarted but after restarting database showing
    “RESTROING” mode very long time . i checked sql server log

    2011-04-30 10:34:30.94 spid20s The database ‘xxx’ is marked RESTORING and is in a state that does not allow recovery to be run.
    2011-04-30 10:34:30.94 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
    2011-04-30 10:34:30.94 spid5s Recovery is complete. This is an informational message only. No user action is required.
    2011-04-30 10:34:31.21 spid51 Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.
    2011-04-30 10:34:31.54 spid51 Using ‘xpsqlbot.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_qv’. This is an informational message only; no user action is required.
    2011-04-30 10:34:31.77 spid51 Using ‘xpstar90.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_instance_regread’. This is an informational message only; no user action is required.
    2011-04-30 10:34:31.88 spid51 Using ‘xplog70.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_msver’. This is an informational message only; no user action is required.
    2011-04-30 10:34:48.93 spid52 Configuration option ‘Agent XPs’ changed from 1 to 0. Run the RECONFIGURE

    after this no log showing and database still in restoring mdoe

  7. how to check backup of the database status like % of complete backup in sql server. Can you give me t-sql statement for this.

  8. Hi,
    I have 3 servers. In all the server my database is showing 0 as status . What does status 0 means.
    Does 0 means its online??

    In all the 3 serveres my model database status value is ‘1073741840’

    If i use the databasepropertyex funtion it shows me the database is online

    Please can you help me in this

  9. How to check the procedures status ( Active/Invalid) in my Database?
    Im My database(schema) having more than 50 procedures , before runing script I want to check their proc status ?Please help me on this .

  10. I got it and i am posting the aswer under my question

    SELECT name FROM sys.databases WHERE state != 0;


    SELECT name, state_desc FROM sys.databases WHERE state IN (0,1,2,3,4,5,6)


  11. Hi,

    I am working on Sql Server 2005. I want to know System Scripts (Sql) for below task.

    Data Monitoring – Data Buffering Quality, DD cache Quality, SQL Pin ratio, Alloc fault rate

    Check System logs for errors or warning

    Check failed update requests

    Check for old pending locks or failed locks

    Check Database Growth

    Check Missing Indexes

    Check table space used

    Database growth and storage plan

    Data Archival

    Check DMS Status and connectivity

    Applications Monitoring and Management

    Application Alerts for Work Process Monitoring

    Daily Monitoring checklist for DB

    Buffer ,Cache Hits ,Clearing of Caches

    SQL Hardening

    If anyone know plz let me know.


  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  13. How to make it work through linked server.

    name,DATABASEPROPERTYEX(name, ‘Status’)as status,FROM

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