SQL Database Status with sys.databases

09 December,2011 by Tom Collins

While writing a  Powershell script for a SQL Server checkout procedure after a reboot , I needed to query the SQL Server database to find the Database Status. Use  state_desc column in sys.databases view to report on the database status.

The descriptions , courtesy of BOL are:

ONLINE  - Database is available for access

RESTORING – One or more files are being restored

RECOVERING – Database is being recovered

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

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

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

OFFLINE – Database is unavailable

SELECT name, state_desc as Database_status
FROM sys.databases

Read More

SQL Server – read only database status

SQL Server Database Status,monitoring and Restores

MSDB Suspect pages table and bad - SQL Server DBA

SQL Server Database SUSPECT state - SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Database Status with sys.databases

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer