Is the SQL Server IsShutdown property useful to determine whether a database is in a good state? [duplicate]

Question

My company has a tool that monitors statuses on servers, services, databases, etc. We monitor a number of on-site servers for our customers. One particular simple check performed is to determine whether a SQL Server database is in a 'good' state by querying for the value of certain database properties. The four database properties we monitor are:

• IsSuspect
• IsOffline
• IsEmergencyMode
• IsShutdown

This is the query we use:

SELECT name AS [SuspectDB],
  DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
  DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
  ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) AS [Shutdown],
  DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
   OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
   OR (ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) = 1)
   OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)

In testing an upgrade to SQL Server 2008, it seems that quite a few of our databases are returning a 1 (true) value for the IsShutdown property. This was never the case previously with SQL Server 2005. The MSDN documentation for the property simply states "Database encountered a problem at startup".

As far as I can tell, the databases are perfectly fine. They are up, can be queried, etc. No issues.

Does the IsShutdown property really matter for my monitoring purposes, i.e., does it indicate that the database is in a bad state? Or should I just remove it from my query?

NOTE: In talking to one of our resident DBAs, they found that on some of our new SQL Server 2008 databases, the fact that the IsAutoClose property is enabled might have something to do with the reason for these databases having an IsShutdown of true. Disabling IsAutoClose seems to "fix" the IsShutdown being true.

Answer 1

A better practice might be to look at the status of the database in sys.databases or sysdatabases. If the status is not 0 then there is probably a problem (unless you are using mirroring, log shipping, etc. where the database status shouldn't be 0).

Answer 2

Ok, after much investigation into this, this is my conclusion:

Short story: The IsShutdown property is not important for monitoring the status of my databases. Even when set to True, the database is still in a good state.

Long story: The MSDN definition for the IsShutdown property is incorrect. It reads:

• Database encountered a problem at startup

That definition does not make much sense given the name of the property. In practice, the IsShutdown property seems to be directly related to the IsAutoClose property. If Auto Close has cleanly shut down the database after no connections are active any longer, IsShutdown gets set to True. Once the database spins back up (so to speak), IsShutdown is set back to False.

This theory is backed up by the fact that there is an is_cleanly_shutdown column in sys.databases. The value of that column is always the same as the value of IsShutdown, i.e., they are the same.

The bad definition of the property is likely what caused the developer I inherited this query from to include it in his database status check. I have now removed the check of that property from the query.

Answer 3

I think you may have answered your own question with the note about AutoClose. One thing I find odd though is that you're checking these properties via the compatability view (sysdatabases) rather than getting the status via the system view (sys.databases). Is there a specific reason for that? My guess is that you're still supporting SQL 2000 instances, but if not you might want to update your monitoring code to take advantage of all the data available in the SQL 2005+ system views.

For slightly better performance consider disabling auto close unless you have a high number of databases on the system and really need to reclaim the resources.