MSSQL 2016: Database cannot be opened due to inaccessible files or insufficient memory or disk space

Microsoft.SQLServer.2016.Database_cannot_be_opened_due_to_inaccessible_files_or_insufficient_memory_or_disk_space._See_the_SQL_Server_errorlog_for_details_1_5_Rule (Rule)

Error 945 is returned when the database is marked IsShutdown . This occurs when a database cannot be recovered due to missing files, or some other resource error that usually can be corrected easily.

Knowledge Base article:

Summary

Error 945 is returned when the database is marked IsShutdown . This occurs when a database cannot be recovered due to missing files, or some other resource error that usually can be corrected easily.

Resolutions

First, verify that the database is marked IsShutdown using DATABASEPROPERTY.

Then, determine the cause of the error by consulting the errorlog, and take action as noted below.

Make the files available and bring the database OFFLINE using ALTER DATABASE.
Use ALTER DATABASE to bring the database ONLINE

First, verify that the database is marked IsShutdown using DATABASEPROPERTY

Then, determine the cause of the error by consulting the errorlog, and take action as noted below.

If one or more data or log files are missing:

Make the files available and bring the database OFFLINE using ALTER DATABASE.
Use ALTER DATABASE to bring the database ONLINE.

If insufficient log space:

Use sp_add_log_file_recover_suspect_db() to add another log file

The database is recovered and brought online by this procedure.

If insufficient data space:

Use sp_add_data_file_recover_suspect_db() to add another log file

The database is recovered and brought online by this procedure.

If insufficient memory:

The insufficient memory error can occur when a number of databases are recovered at the same time. Retrying the operation may fix the problem:

Retry the operation using ALTER DATABASE to bring the database OFFLINE
Use ALTER DATABASE to bring the database ONLINE

If retrying the operation does not work, consider freeing up memory.

Overrideable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	Yes
Priority	Defines Alert Priority.	1
Severity	Defines Alert Severity.	1

Element properties:

Target

Microsoft.SQLServer.2016.DBEngine

Category

EventCollection

Enabled

True

Event_ID

945

Event Source

$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Remotable

True

Alert Message

MSSQL 2016: Database cannot be opened due to inaccessible files or insufficient memory or disk space

{0}

Event Log

Application

Comment

Mom2016ID='{3BC2D536-13F6-4982-B717-0DCF03213119}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}

Member Modules:

ID	Module Type	TypeId	RunAs
_F6DA1507_12AF_11D3_AB21_00A0C98620CE_	DataSource	Microsoft.Windows.EventProvider	Default
GenerateAlert	WriteAction	System.Health.GenerateAlert	Default

Source Code:

<Rule ID="Microsoft.SQLServer.2016.Database_cannot_be_opened_due_to_inaccessible_files_or_insufficient_memory_or_disk_space._See_the_SQL_Server_errorlog_for_details_1_5_Rule" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" Enabled="true" ConfirmDelivery="true" Remotable="true" Comment="Mom2016ID='{3BC2D536-13F6-4982-B717-0DCF03213119}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="_F6DA1507_12AF_11D3_AB21_00A0C98620CE_" Comment="{F6DA1507-12AF-11D3-AB21-00A0C98620CE}" TypeID="Windows!Microsoft.Windows.EventProvider">

      <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

      <LogName>Application</LogName>

      <Expression>

        <And>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>PublisherName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>EventDisplayNumber</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>945</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </And>

      </Expression>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">

      <Priority>1</Priority>

      <Severity>1</Severity>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2016.Database_cannot_be_opened_due_to_inaccessible_files_or_insufficient_memory_or_disk_space._See_the_SQL_Server_errorlog_for_details_1_5_Rule.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

        <AlertParameter1>Event ID: $Data/EventDisplayNumber$. $Data/EventDescription$</AlertParameter1>

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>