This monitor checks the status of the Replication Merge Agent service for Subscription. 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.
The Replication Merge Agent is an executable that applies the initial snapshot held in the database tables to the Subscribers. It also merges incremental data changes that occurred at the Publisher after the initial snapshot was created. The agent also reconciles conflicts using either the configured rules or uses a custom resolver. 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.
A merge agent can fail because of the following reasons:
The Merge Agent cannot connect the Publisher database(pull subscription)
The Merge Agent cannot connect to the Subscriber database(push subscription)
The merge replication metadata is deleted at the Subscriber because the merge agent has not synchronized within the retention period
The subscription is marked as expired.
Query timeout errors
Network connectivity errors
The Merge agent cannot start because of memory exhaustion for the non-interactive desktop heap
Incorrect parameters passed to the agent executable
This issue can be resolved by ensuring the following:
The merge agent cannot connect to both Publisher and Subscriber. Configure access to dbo to the merge agent account on both the Publisher and Subscriber databases
If the subscription is marked as expired, reinitialize the subscription
Resolve network or connectivity issues between the servers when the merge agent is running
If the subscription metadata is deleted at the Publisher, drop and recreate the subscription. This can happen when the merge agent has not synchronized with the Subscriber/Publisher in double the retention period.
Ensure the correct parameters are passed to the agent executable.
Increase the desktop heap as mentioned in http://support.microsoft.com/kb/949296
Query the msmerge_agents and msmerge_history tables on the distribution database to get more information on the agent failure. The Msrepl_errors table also contains information about any agent failures.
Desktop heap issue
http://support.microsoft.com/kb/949296
Replication Merge agent
http://msdn.microsoft.com/library/ms147839.aspx
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 |
| Target | Microsoft.SQLServer.Replication.Windows.Subscription | ||
| 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.Replication.Windows.MonitorType.ReplicationAgentState | ||
| Remotable | True | ||
| Accessibility | Public | ||
| Alert Message |
| ||
| RunAs | Microsoft.SQLServer.Core.RunAs.Monitoring |
Home
<UnitMonitor ID="Microsoft.SQLServer.Replication.Windows.Monitor.SubscriptionMergeAgentState" Accessibility="Public" Enabled="true" Target="SQLReplWD!Microsoft.SQLServer.Replication.Windows.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Replication.Windows.MonitorType.ReplicationAgentState" ConfirmDelivery="false" RunAs="SqlCoreLib!Microsoft.SQLServer.Core.RunAs.Monitoring">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Replication.Windows.Monitor.SubscriptionMergeAgentState.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='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/MachineName$</MachineName>
<InstanceName>$Target/Host/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</InstanceName>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<ConnectionString>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription']/Distributor$</ConnectionString>
<MonitoringType>$Target/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<XPathName>Merge$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription']/Distributor$$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscriber']/ConnectionString$$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription']/SubscriptionName$</XPathName>
<ShowJobsWithUnknownState>false</ShowJobsWithUnknownState>
<EstimatedJobDuration>15</EstimatedJobDuration>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>200</TimeoutSeconds>
<CheckMachineAndInstanceName>false</CheckMachineAndInstanceName>
</Configuration>
</UnitMonitor>