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?

  • Note that DATABASEPROPERTY() function is deprecated, and will stop working in SQL Server "Denali" - so I recommend checking your code for any references and changing them to queries against sys.databases, or DATABASEPROPERTYEX() if absolutely necessary (not sure how much longer that one will stay around). – Aaron Bertrand Apr 27 '11 at 12:23

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:

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

| improve this answer | |

I'd use the newer sys.databases not sydatabases but otherwise this is OK

Not least, you don't need DATABASEPROPERTY calls

   name, state_desc
   state IN (4, 5, 6)
| improve this answer | |

the way that I found to see the db status is to use the function DATABASEPROPERTYEX ( database , property ), like this:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status').

The statuses are pretty self explanatory:

ONLINE = Database is available for query.

OFFLINE = Database was explicitly taken offline.

RESTORING = Database is being restored.

RECOVERING = Database is recovering and not yet ready for queries.

SUSPECT = Database did not recover.

EMERGENCY = Database is in an emergency, read-only state. Access is restricted to sysadmin members

In Ola Hallengren's blog (an SQL MVP), in his tool to verify the database integrity, I found he's using the view sys.database_recovery_status to query a db status. If the db has a row in this view, then it's live and kicking, if not, it's offline.

PS: the databaseproperty function that you use is going to be removed in future versions, so databasepropertyex is replacing it.

| improve this answer | |
  • I don't think the asker was looking for specific recovery information, but rather a query for a general health check. sys.databases is the better spot to find the db status assuming you're running with sufficient privileges to see offline databases. If it's not in sys.databases it's dropped or detached. – AndrewSQL Jan 19 '11 at 19:45
  • 1
    Yes, I know what you mean, but I said that he was just checking if the db exists in that view, if yes, than he treated the database as accessible, if not, he treated the db as inaccessible. So this can be a second check :). To me, a fine starting health check would be to query any single small table from that db and if that query returns any data, I'd consider the db accessible at first sight. – Marian Jan 19 '11 at 19:55

Rather than query for specific conditions, I'd take a look at anything in sys.databases where state_desc <> 'ONLINE'. Also, depending on what you're looking to do, has_dbaccess might throw some false positives.

| improve this answer | |

I like this one:

SELECT name, state_desc 
FROM   sys.databases

name                            state_desc
------------------------------- ----------------
master                          ONLINE
tempdb                          ONLINE
model                           ONLINE
msdb                            ONLINE
TestTableDatabase2005           RECOVERY_PENDING
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.