• Article

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.