Replication Log Reader Agent State for Publication

Microsoft.SQLServer.2012.Replication.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 reasons such 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
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.	300
Show Jobs With Unknown State	Include jobs with unknown state to the monitor output and alert context. Will affect health	No
Timeout (seconds)	Timeout (seconds)	300
Synchronization Time	Synchronization Time

Element properties:

Target

Microsoft.SQLServer.2012.Replication.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.2012.Replication.MonitorType.ReplicationAgentState

Remotable

True

Accessibility

Internal

Alert Message

MSSQL2012 Replication: Replication Log Reader agent has an error for Publication.

{0}

RunAs

Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2012.Replication.Monitor.PublicationLogReaderAgentState" Accessibility="Internal" Enabled="true" Target="MS2RD!Microsoft.SQLServer.2012.Replication.Publication" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2012.Replication.MonitorType.ReplicationAgentState" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2012.Replication.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>

    <SqlTimeout>300</SqlTimeout>

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

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

    <ShowJobsWithUnknownState>false</ShowJobsWithUnknownState>

    <EstimatedJobDuration>15</EstimatedJobDuration>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>