Replication Log Reader Agent State for Publication

Microsoft.SQLServer.2017.Replication.Windows.Monitor.PublicationLogReaderAgentState (UnitMonitor)

This monitor checks the status of the Replication Log Reader Agent service for Publications.

Knowledge Base article:

Summary

The Replication Log Reader Agent is an executable that monitors the transaction log of each database configured for transactional replication. The Log Reader Agent copies the transactions marked for replication from the transaction log into the distribution database.

Causes

A Log Reader agent can fail for the reasons described below:

The Log Reader agent cannot connect to the Publisher database or to the Distribution database.
The Log Reader Agent cannot execute sp_replcmds/sp_replcounters on the Publisher database.
The Log Reader Agent cannot execute sp_repldone on the Publisher database.
The Log Reader Agent failed with query timeout errors.
Memory exhaustion for the non-interactive desktop heap.
Incorrect parameters passed to the agent executable.

Resolutions

This issue may be resolved by the following:

Granting dbo access to the Publisher login.
Increasing the query timeout parameter value and ensuring the Log Reader Agent is not blocked or waiting on any resources
Executing sp_replcmds manually on the Publisher database using the Publisher login account.
Execute sp_replflush to clear any other clients that are running replication stored procedures on the Publisher database.
Verify the correct parameters are passed to the agent executable.
Increase the desktop heap as mentioned in http://support.microsoft.com/kb/949296

External

http://technet.microsoft.com/library/ms146878.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
Estimated Job Duration	Threshold which is being used to check the job schedule compliance	15
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.	300
Show Jobs with Unknown State	Include jobs with unknown state to the monitor output and alert context. Will affect health.	No
Synchronization Time	Synchronization Time
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	200
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.2017.Replication.Windows.Publication

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2017.Replication.Windows.MonitorType.ReplicationAgentState

Remotable

True

Accessibility

Internal

Alert Message

MSSQL 2017 on Windows Replication: Replication Log Reader agent has an error for Publication.

{0}

RunAs

Microsoft.SQLServer.MonitoringAccount

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2017.Replication.Windows.Monitor.PublicationLogReaderAgentState" Accessibility="Internal" Enabled="true" Target="SQL2017ReplWD!Microsoft.SQLServer.2017.Replication.Windows.Publication" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2017.Replication.Windows.MonitorType.ReplicationAgentState" ConfirmDelivery="false" RunAs="GPMP!Microsoft.SQLServer.MonitoringAccount">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2017.Replication.Windows.Monitor.PublicationLogReaderAgentState.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='Message']$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

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

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Host/Host/Property[Type='SQL2017L!Microsoft.SQLServer.2017.Library.DBEngine']/MachineName$</MachineName>

    <InstanceName>$Target/Host/Host/Property[Type='SQL2017L!Microsoft.SQLServer.2017.Library.DBEngine']/InstanceName$</InstanceName>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <ConnectionString>$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericPublisher']/Distributor$</ConnectionString>

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

    <XPathName>Log reader$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericPublisher']/Distributor$$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericPublisher']/DistributorDatabaseName$$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericPublisher']/ConnectionString$$Target/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericPublication']/PublicationName$</XPathName>

    <ShowJobsWithUnknownState>false</ShowJobsWithUnknownState>

    <EstimatedJobDuration>15</EstimatedJobDuration>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>200</TimeoutSeconds>

    <CheckMachineAndInstanceName>false</CheckMachineAndInstanceName>

  </Configuration>

</UnitMonitor>