What is the best query to use to monitor a SQL Server database's status?

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:

SELECT 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?

If you're using SQL 2005+ and only want to return the DB name where the DB isn't in the "ONLINE" state I'd use this:

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

Remember that databases participating in mirroring or log shipping will not be online or may change state regularly. For more info about the sys.databases DMV see documentation here: http://msdn.microsoft.com/en-us/library/ms178534.aspx

StackExchange Database Administrators Q#712, answer score: 14