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)

ONLINE
Database is available for access.

OFFLINE
Database is unavailable.

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

RECOVERING
Database is being recovered.

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

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

EMERGENCY
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)

Example:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks'
GO

ResultSet:
DatabaseStatus_DATABASEPROPERTYEX
——————————————————
ONLINE

DatabaseStatus_sysDatabase
——————————————————
ONLINE

2) Using SQL Server Management Studio

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

SQL SERVER - 2005 - Find Database Status Using sys.databases or DATABASEPROPERTYEX status
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL Database Status, BOL sys.databases, BOL DATABASEPROPERTYEX

SQL Scripts, SQL Server DBCC, SQL System Table, SQL Utility
Previous Post
SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS
Next Post
SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

Related Posts

30 Comments. Leave new

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

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

    ——————————OR—————————————

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

    Where 0 = ONLINE ,1 = RESTORING,2 = RECOVERING,3 = RECOVERY_PENDING,4 = SUSPECT,5 = EMERGENCY, 6 = OFFLINE

    Reply
  • 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.

    Thanks…

    Reply
  • santosh kumar chaudhary
    December 24, 2011 1:17 pm

    how to check the last database shrinking status in sql server 2000 and 2005 history

    Reply
  • How to make it work through linked server.

    SELECT
    name,DATABASEPROPERTYEX(name, ‘Status’)as status,FROM
    [USPVL8C14].master.dbo.sysdatabases

    Reply

Leave a Reply