MSSQL 2016: A SQL job failed to complete successfully

Microsoft.SQLServer.2016.A_SQL_job_failed_to_complete_successfully_1_5_Rule (Rule)

A SQL Server Agent Job Failed. The SQL Server Agent is responsible for running SQL Server tasks scheduled to occur at specific times or intervals as well as detecting specific conditions for which administrators have defined an action, such as alerting someone through pages or e-mail, or a task that will address the conditions. The SQL Server Agent is also used for running replication tasks defined by administrators. Note: This rule is disabled by default. Please use overrides to enable it when necessary.

Knowledge Base article:

Summary

Configuration

This rule detects failure only for jobs that have event log notification about job fail. To configure job with event log notification, you can do the following:

Run SQL Server Management Studio.
Expand a server group; then expand a server.
Expand Management; then expand SQL Server Agent.
Right-click the appropriate job and choose 'Properties'.
Select section "Notifications".
Enable checkbox "Write to the Windows Application event log".
Select "When the job fails" or "When the job completes".

Resolutions

To troubleshoot this failure review the event associated with the alert to determine the specific jobs and job steps that failed. Also, check historical outcomes of the job to determine the last date the job worked. To the job execution history, you can do the following:

Run SQL Server Management Studio.
Expand a server group; then expand a server.
Expand Management; then expand SQL Server Agent.
Right-click the appropriate job and choose 'View Job History'.

Also, check that the service account used by SQL Server Agent is a member of the Domain Users group. The LocalSystem account does not have network access rights, so if your jobs require resources across the network, or if you want to notify operators through e-mail or pagers, you must set the account the SQL Server Agent service runs under to be a member of the Domain Users group.

Overrideable Parameters

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

Element properties:

Target

Microsoft.SQLServer.2016.Agent

Category

EventCollection

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Remotable

True

Alert Message

MSSQL 2016: A SQL job failed to complete successfully

{0}

Event Log

Application

Comment

Mom2016ID='{8CCE3391-B79E-4182-922E-BB540ED8396E}';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.A_SQL_job_failed_to_complete_successfully_1_5_Rule" Target="SQL2016Core!Microsoft.SQLServer.2016.Agent" Enabled="false" ConfirmDelivery="true" Remotable="true" Comment="Mom2016ID='{8CCE3391-B79E-4182-922E-BB540ED8396E}';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/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>208</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>PublisherName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

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

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>Params/Param[3]</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>Failed</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.A_SQL_job_failed_to_complete_successfully_1_5_Rule.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

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

      </AlertParameters>

      <Suppression>

        <SuppressionValue>$Data/Params/Param[1]$</SuppressionValue>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>