How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)

Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the same monitoring data programmatically using replication stored procedures. These stored procedures enable you to program the following tasks:

  • Monitor the state of Publishers, publications, and subscriptions.

  • Monitor Merge Agent sessions at one or more Subscribers.

  • Monitor transactional commands waiting to be applied at one or more Subscribers.

  • Define the threshold metrics that determine when a publication requires intervention.

To monitor Publishers, publications, and subscriptions from the Distributor

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublisher. This returns monitoring information for all Publishers using this Distributor. To limit the result set to a single Publisher, specify **@publisher**.

  2. At the Distributor on the distribution database, execute sp_replmonitorhelppublication. This returns monitoring information for all publications using this Distributor. To limit the result set to a single Publisher, publication, or published database, specify **@publisher**, **@publication**, or **@publisher\_db**, respectively.

  3. At the Distributor on the distribution database, execute sp_replmonitorhelpsubscription. This returns monitoring information for all subscriptions using this Distributor. To limit the result set to subscriptions belonging to a single Publisher, publication, or published database, specify **@publisher**, **@publication**, or **@publisher\_db**, respectively.

To monitor transactional commands waiting to be applied at the Subscriber

  • At the Distributor on the distribution database, execute sp_replmonitorsubscriptionpendingcmds. This returns monitoring information for all commands pending for all subscriptions using this Distributor. To limit the result set to commands pending for subscriptions belonging to a single Publisher, Subscriber, publication, or published database, specify **@publisher**, **@subscriber**, **@publication**, or **@publisher\_db**, respectively.

To monitor merge changes waiting to be uploaded or downloaded

  1. At the Publisher on the publication database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to Subscribers. To limit the result set to changes that belong to a single publication or article, specify **@publication** or **@article**, respectively.

  2. At a Subscriber on the subscription database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to the Publisher. To limit the result set to changes that belong to a single publication or article, specify **@publication** or **@article**, respectively.

To monitor Merge Agent sessions

  1. At the Distributor on the distribution database, execute sp_replmonitorhelpmergesession. This returns monitoring information, including Session_id, on all Merge Agent sessions for all subscriptions using this Distributor. You can also obtain Session_id by querying the MSmerge_sessions system table.

  2. At the Distributor on the distribution database, execute sp_replmonitorhelpmergesessiondetail. Specify a Session_id value from step 1 for **@session\_id**. This displays detailed monitor information about the session.

  3. Repeat step 2 for each session of interest.

To monitor Merge Agent sessions for pull subscriptions from the Subscriber

  1. At the Subscriber on the subscription database, execute sp_replmonitorhelpmergesession. For a given subscription, specify **@publisher**, **@publication**, and the name of the publication database for **@publisher\_db**. This returns monitoring information for the last five Merge Agent sessions for this subscription. Note the value of Session_id for sessions of interest in the result set.

  2. At the Subscriber on the subscription database, execute sp_replmonitorhelpmergesessiondetail. Specify a Session_id value from step 1 for **@session\_id**. This displays detailed monitoring information about the session.

  3. Repeat step 2 for each session of interest.

To view and modify the monitor threshold metrics for a publication

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublicationthresholds. This returns the monitoring thresholds set for all publications using this Distributor. To limit the result set to monitor thresholds to publications belonging to a single Publisher or published database or to a single publication, specify **@publisher**, **@publisher\_db**, or **@publication**, respectively. Note the value of Metric_id for any thresholds that must be changed. For more information, see Setting Thresholds and Warnings in Replication Monitor.

  2. At the Distributor on the distribution database, execute sp_replmonitorchangepublicationthreshold. Specify the following as needed:

    • The Metric_id value obtained in step 1 for **@metric\_id**.

    • A new value for the monitor threshold metric for **@value**.

    • A value of 1 for **@shouldalert** for an alert to be logged when this threshold is reached, or a value of 0 if an alert is not needed.

    • A value of 1 for **@mode** to enable the monitor threshold metric or a value of 2 to disable it.