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

sql server

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.

Best Answer

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