Home
  •  

MSSQL 2016: SQL Server Out Of Memory

Microsoft.SQLServer.2016.EventCollectionRule.DBEngine.SQLServerOutOfMemory (Rule)

SQL Server has failed to allocate the sufficient amount of memory to run the query.

Knowledge Base article:

Summary

SQL Server has failed to allocate the sufficient amount of memory to run the query.

Causes

The issue may be caused by a variety of reasons including operating system settings, physical memory availability, or memory limits on the current workload. In most cases, the transaction that failed is not the cause of this error. Diagnostic queries, such as DBCC statements, may fail because server the does not have sufficient memory

SQL Server Memory-Optimized Data uses more memory and in different ways than SQL Server does. It is possible that the amount of memory you installed and allocated for Memory-Optimized Data becomes inadequate for your growing needs. If so, you could run out of memory.

Resolutions

See the topic Monitor and Troubleshoot Memory Usage for guidance that can help you avoid many OOM situations.

External

See more detailed information about this issue: Resolve OOM (Out Of Memory) issues

Overrideable Parameters

Name

Description

Default Value

Enabled

Enables or disables the workflow.

Yes

Priority

Defines Alert Priority.

1

Severity

Defines Alert Severity.

2

Element properties:

TargetMicrosoft.SQLServer.2016.DBEngine
CategoryEventCollection
EnabledTrue
Event_ID701
Event Source$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
RemotableTrue
Alert Message
MSSQL 2016: SQL Server has failed to allocate sufficient memory to run the query
{0}
Event LogApplication
CommentMom2016ID='{2531CFA5-7EF0-4B45-AAA1-DAE73294DFF6}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}

Member Modules:

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

Source Code:

<Rule ID="Microsoft.SQLServer.2016.EventCollectionRule.DBEngine.SQLServerOutOfMemory" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" Enabled="true" ConfirmDelivery="true" Remotable="true" Comment="Mom2016ID='{2531CFA5-7EF0-4B45-AAA1-DAE73294DFF6}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="DS" 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>EventDisplayNumber</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>701</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

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

        </And>

      </Expression>

    </DataSource>

  </DataSources>

  <WriteActions>

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

      <Priority>1</Priority>

      <Severity>2</Severity>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2016.EventCollectionRule.DBEngine.SQLServerOutOfMemory.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

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

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>