title: Is the SQL Server IsShutdown property useful to determine whether a database is in a good state?
body: <p>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:</p>
  
  <ul>
  <li>IsSuspect</li>
  <li>IsOffline</li>
  <li>IsEmergencyMode</li>
  <li>IsShutdown</li>
  </ul>
  
  <p>This is the query we use:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 <strong>IsShutdown</strong> property. This was never the case previously with SQL Server 2005. The <a href="http://msdn.microsoft.com/en-us/library/ms176049.aspx" rel="nofollow">MSDN documentation</a> for the property simply states "Database encountered a problem at startup".</p>
  
  <p>As far as I can tell, the databases are perfectly fine. They are up, can be queried, etc. No issues. </p>
  
  <p>Does the <strong>IsShutdown</strong> 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? </p>
  
  <p>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 <strong>IsAutoClose</strong> property is enabled might have something to do with the reason for these databases having an <strong>IsShutdown</strong> of true. Disabling <strong>IsAutoClose</strong> seems to "fix" the <strong>IsShutdown</strong> being true. </p>