GAPTHEGURU

Geek with special skills

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

The IT department at a customer had a lot off SCOM error messages from several databases that reported wrong database status, I came across sys.databases and DATABASEPROPERTYEX. It is a very easy way to check the database status. Follow this query.

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.

SELECT DATABASEPROPERTYEX('Databasename', 'Status')

DatabaseStatus_DATABASEPROPERTYEX

GO

SELECT state_desc DatabaseStatus_sysDatabase

FROM sys.databases

WHERE name = 'Databasename'

GO

ResultSet:

DatabaseStatus_DATABASEPROPERTYEX

——————————————————

ONLINE

DatabaseStatus_sysDatabase

——————————————————

ONLINE

Advertisements

01/06/2012 Posted by | Sql Server | , , , | Leave a comment

   

%d bloggers like this: