20

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 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. – CoderHawk 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' :) – CoderHawk Mar 4 '11 at 12:26
12

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

9

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)
2

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.

  • 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
2

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.

0

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

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.