Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.

This is the query that I inherited for this check:

    name AS [SuspectDB],
    DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
    DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
    DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency],
    has_dbaccess(name) AS [HasDBAccess]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
    OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
    OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)
    OR (has_dbaccess(name) = 0)

If that query returns any results, the assumption being made is that the database is in a suspect or potentially bad state.

Is there a better way to do this?


merged by Jeff Atwood Jan 29 '11 at 1:20

This question was merged with What is the best query to use to monitor a SQL Server database's status? because it is an exact duplicate of that question.

migrated from stackoverflow.com Jan 20 '11 at 14:56

This question came from our site for professional and enthusiast programmers.