Replication Snapshot Agent State

Microsoft.SQLServer.2008.Replication.Monitor.PublicationSnapshotAgentState (UnitMonitor)

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

Knowledge Base article:

Summary

This monitor checks the status of the Replication Snapshot Agent service for publications. The Snapshot Agent prepares snapshot files containing the schema and data of published tables and database objects, stores the files in the snapshot folder for the Publisher, and records tracking information in the distribution database on the Distributor.

Causes

A snapshot agent can fail because of any of the following reasons:

No free space available in the snapshot folder.
Missing write permissions on the snapshot folder
The Snapshot agent cannot connect to the Publisher database.
The Snapshot agent cannot connect to the distribution database.
Query timeout errors

Resolutions

To resolve this agent error issue ensure the following:

The snapshot agent account has read/write permissions on the snapshot folder
There is enough free space available on the media for the snapshot folder
The Publisher login is a member of db_owner fixed database role
Distributor login is a member of db_owner fixed database role
The Snapshot agent is not being blocked by any other process (SPID) nor waiting on any wait_type. This may be caused by resource contention.

Additional

The mssnapshot_agents and mssnapshot_history tables can be queried on the distribution database to get more information on the agent failure. The Msrepl_errors table also contains information about any agent failures.

External

http://technet.microsoft.com/library/ms146939.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.2008.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.2008.Replication.MonitorType.ReplicationAgentState

Remotable

True

Accessibility

Public

Alert Message

MSSQL2008 Replication: Replication Snapshot agent has an error.

{0}

RunAs

Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.Replication.Monitor.PublicationSnapshotAgentState.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>Snapshot$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>