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
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.
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.
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
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.
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
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.
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.
Repeat step 2 for each session of interest.
To monitor Merge Agent sessions for pull subscriptions from the Subscriber
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.
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.
Repeat step 2 for each session of interest.
To view and modify the monitor threshold metrics for a publication
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.
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.