SQL Server Agent State for Subscriber

Microsoft.SQLServer.Replication.Windows.Monitor.Subscriber.SQLServerAgentState (UnitMonitor)

This monitor checks if the SQL Server Agent is running on Subscriber. Note that SQL Server Agent Windows Service is not supported by any edition of SQL Server Express. Therefore, this monitor is not applicable for SQL Server Express cases.

Knowledge Base article:

Summary

This monitor connects to the Replication Subscriber and verifies the SQL Server Agent service is running. The SQL Server Agent service is installed automatically during the SQL Server setup and is responsible for starting the replication agents. Note that SQL Server Agent Windows Service is not supported by any edition of SQL Server Express. Therefore, this monitor is not applicable for SQL Server Express cases.

Causes

The SQL Agent Service may have been manually stopped by Windows System Administrator or stopped as result of a failure in the Agent. Examine the Windows Application Event log for possible causes for an unexpected stopped Agent service.

If the start mode of the SQL Server Agent Service is set to "Manual" instead of "Automatic", the service will not automatically restart upon reboot of the server.

Resolutions

Start the SQL Server Agent service under Windows Services, SQL Server Configuration Manager, or via SQL Server Management Studio. If Agent "Start Mode" is set to "Manual", consider changing to "Automatic".

Start, Stop, or Pause the SQL Server Agent Service

http://technet.microsoft.com/library/ms190695.aspx

Additional

Distribution Agents running on the Subscriber are called PULL Agents. These can run continuously or on scheduled intervals. If they run at scheduled intervals, the SQL Agent Service, running continuously, will schedule the Distribution Agent startup. If the SQL Agent Service is stopped, then Distribution Pull Agents will not start as scheduled resulting in data latency.

External

Use the link below to view the SQL Server Agent Error Log: https://msdn.microsoft.com/library/ms175488.aspx

This topic describes how to configure Microsoft SQL Server Agent to automatically restart if it should stop unexpectedly in SQL Server on Windows: https://msdn.microsoft.com/library/ms178130.aspx

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	Error
Enabled	Enables or disables the workflow.	Yes
Generates Alerts	Defines whether the workflow generates an Alert.	Yes
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	600
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.	15

Element properties:

Target

Microsoft.SQLServer.Replication.Windows.Subscriber

Parent Monitor

System.Health.AvailabilityState

Category

AvailabilityHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Replication.Windows.MonitorType.SqlServiceStatus

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows Replication: SQL Server Agent Windows Service on Subscriber is stopped.

The SQL Server Agent windows service is stopped on SQL Server Instance {1} on computer {0}.

RunAs

Microsoft.SQLServer.Core.RunAs.Monitoring

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Replication.Windows.Monitor.Subscriber.SQLServerAgentState" Accessibility="Public" Enabled="true" Target="SQLReplWD!Microsoft.SQLServer.Replication.Windows.Subscriber" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Replication.Windows.MonitorType.SqlServiceStatus" ConfirmDelivery="false" RunAs="SqlCoreLib!Microsoft.SQLServer.Core.RunAs.Monitoring">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Replication.Windows.Monitor.Subscriber.SQLServerAgentState.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/MachineName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Running" HealthState="Success"/>

    <OperationalState ID="Warning" MonitorTypeStateID="RunningManualOrUnableToDetectStatus" HealthState="Warning"/>

    <OperationalState ID="Error" MonitorTypeStateID="NotRunning" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/MachineName$</MachineName>

    <InstanceName>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</InstanceName>

    <ConnectionString>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

    <MonitoringType>$Target/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <IntervalSeconds>600</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>