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

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'I开发者_如何学运维sShutdown'), 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.

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.

IsShutDown, which is set if SQL Server is unable to open a database's files during startup

So this would be a good issue to know if you rebooted your server and somebody had moved your database files or they didn't open because of a disk I/O problem.

I would say that ONLINE and MULTI_USER is the only good state you'd want your database to be in.

select state_desc,user_access_desc from sys.databases

Did you read the footnote that is linked to specifically for NULL for that property, on the MSDN page you linked to?

Returned value is also NULL if the database has never been started or has been autoclosed.

And so yes, if you change the AutoClose property, you'll get different results. Coalescing NULL to 1 on this property seems like a bad decision - I'd remove your ISNULL() and ignore NULL values.