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.
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.
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".
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.
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | No |
Priority | Defines Alert Priority. | 1 |
Severity | Defines Alert Severity. | 2 |
Target | Microsoft.SQLServer.2016.Agent | ||
Category | EventCollection | ||
Enabled | False | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Remotable | True | ||
Alert Message |
| ||
Event Log | Application | ||
Comment | Mom2016ID='{8CCE3391-B79E-4182-922E-BB540ED8396E}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74} |
ID | Module Type | TypeId | RunAs |
---|---|---|---|
_F6DA1507_12AF_11D3_AB21_00A0C98620CE_ | DataSource | Microsoft.Windows.EventProvider | Default |
GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
<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>