An SQL job failed to complete successfully

Microsoft.SQLServer.2008.A_SQL_job_failed_to_complete_successfully_1_5_Rule (Rule)

Knowledge Base article:

Summary

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

Configuration

This rule detects failure only for jobs which 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 “Notifications” section.
Enable “Write to the Windows Application event log” checkbox.
Select "When the job fails" or "When the job completes".

Resolutions

To troubleshoot this failure view the events associated with this alert to understand the specific jobs and job steps that failed. Also, check the history values of the job to determine the last date the job worked. To view the history values of the job, 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 account the SQL Server Agent services runs under is a member of the Domain Users group. The Local System 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.2008.Agent

Category

EventCollection

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Remotable

True

Alert Message

An SQL job failed to complete successfully

{0}

Event Log

Application

Comment

Mom2008ID='{8CCE3391-B79E-4182-922E-BB540ED8396E}';MOM2008GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}

Member Modules:

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

Source Code:

<Rule ID="Microsoft.SQLServer.2008.A_SQL_job_failed_to_complete_successfully_1_5_Rule" Target="SQL2008Core!Microsoft.SQLServer.2008.Agent" Enabled="false" ConfirmDelivery="true" Remotable="true" Comment="Mom2008ID='{8CCE3391-B79E-4182-922E-BB540ED8396E}';MOM2008GroupID={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" RunAs="SQL!Microsoft.SQLServer.SQLDefaultAccount">

      <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="SQL!Microsoft.SQLServer.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.2008.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>