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:

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?

share|improve this question
    
If you're interested in more in depth health checks, you might want to monitor some of the more important WMI performance counters such as Deadlocks, Disk IO wait time, transactions per second, etc. –  RQDQ Jan 19 '11 at 18:22
    
@RQDC - If you're going down road in SQL Server 2008 probably easier to set up the Management Datawarehouse. –  Martin Jan 19 '11 at 18:55
    
@brett rogers - 'Best/Good' are considered to be subjective questions. Please re-phrase. –  Coder Hawk Jan 20 '11 at 9:31
    
Please provide a suggestion on how to rephrase and I'll gladly consider it. I'm looking for "best" as in "best practice". Seems like the proper way to phrase it to me. –  brett rogers Jan 21 '11 at 18:17
    
@brett - well! glad that you got the 'best' :) –  Coder Hawk Mar 4 '11 at 12:26
show 1 more comment

4 Answers 4

up vote 9 down vote accepted

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

share|improve this answer
add comment

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

Not least, you don't need DATABASEPROPERTY calls

SELECT
   name, state_desc
FROM
   sys.databases
WHERE
   state IN (4, 5, 6)
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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