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 | , , ,

No comments yet.

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

%d bloggers like this: