--- title: "Programmatically Monitor Replication | Microsoft Docs" description: Replication Monitor is a graphical tool. Use Replication Monitor to monitor a replication topology in SQL Server. ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.prod_service: "database-engine" ms.reviewer: "" ms.technology: replication ms.topic: conceptual dev_langs: - "TSQL" helpviewer_keywords: - "sp_replmonitorhelppublisher" - "sp_replmonitorhelpmergesessiondetail" - "monitoring performance [SQL Server replication], publication status" - "sp_replmonitorhelpmergesession" - "sp_replmonitorhelppublicationthresholds" - "monitoring performance [SQL Server replication], subscription status" - "monitoring performance [SQL Server replication], Transact-SQL programming" - "sp_replmonitorsubscriptionpendingcmds" - "sp_replmonitorchangepublicationthreshold" - "transactional replication, monitoring" - "sp_replmonitorhelppublication" - "sp_replmonitorhelpsubscription" - "monitoring performance [SQL Server replication], thresholds and warnings" - "merge replication monitoring [SQL Server replication]" - "snapshot replication [SQL Server], monitoring" ms.assetid: e8bf8850-8da5-4a4f-a399-64232b4e476d author: "MashaMSFT" ms.author: "mathoma" monikerRange: "=azuresqldb-mi-current||>=sql-server-2016" --- # Programmatically Monitor Replication [!INCLUDE[sql-asdbmi](../../../includes/applies-to-version/sql-asdbmi.md)] Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the same monitoring data programmatically by using [!INCLUDE[tsql](../../../includes/tsql-md.md)] replication stored procedures or replication management objects (RMO). These objects 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. - Monitor the status of tracer tokens. **In this topic:** [Transact-SQL](#Tsql) [Replication Management Objects (RMO)](#RMO) ## Transact-SQL #### To monitor Publishers, publications, and subscriptions from the Distributor 1. At the Distributor on the distribution database, execute [sp_replmonitorhelppublisher](../../../relational-databases/system-stored-procedures/sp-replmonitorhelppublisher-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelppublication-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelpsubscription-transact-sql.md). 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 1. At the Distributor on the distribution database, execute [sp_replmonitorsubscriptionpendingcmds](../../../relational-databases/system-stored-procedures/sp-replmonitorsubscriptionpendingcmds-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-showpendingchanges-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-showpendingchanges-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelpmergesession-transact-sql.md). 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](../../../relational-databases/system-tables/msmerge-sessions-transact-sql.md) system table. 2. At the Distributor on the distribution database, execute [sp_replmonitorhelpmergesessiondetail](../../../relational-databases/system-stored-procedures/sp-replmonitorhelpmergesessiondetail-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelpmergesession-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelpmergesessiondetail-transact-sql.md). 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](../../../relational-databases/system-stored-procedures/sp-replmonitorhelppublicationthresholds-transact-sql.md). 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 [Set Thresholds and Warnings in Replication Monitor](../../../relational-databases/replication/monitor/set-thresholds-and-warnings-in-replication-monitor.md). 2. At the Distributor on the distribution database, execute [sp_replmonitorchangepublicationthreshold](../../../relational-databases/system-stored-procedures/sp-replmonitorchangepublicationthreshold-transact-sql.md). 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. ## Replication Management Objects (RMO) #### To monitor a subscription to a merge publication at the Subscriber 1. Create a connection to the Subscriber by using the class. 2. Create an instance of the class, and set the , , , properties for the subscription, and set the property to the created in step 1. 3. Call one of the following methods to return information on Merge Agent sessions for this subscription: - - returns an array of objects with information on up to the last five Merge Agent sessions. Note the value for any sessions of interest. - - returns an array of objects with information on Merge Agent sessions that have occurred during the past number of hours passed in as the *hours* parameter (up to the last five sessions). Note the value for any sessions of interest. - - returns a object with information on the last Merge Agent session. Note the value for this session. - - returns a object with information on up to the last five Merge Agent sessions, one in each row. Note the value of the **Session_id** column for any sessions of interest. - - returns a object with information on the last Merge Agent session. Note the value of the **Session_id** column for this session. 4. (Optional) Call to refresh the data for the object passed as *mss,* or call to refresh the data in the object passed as *drRefresh*. 5. Using the session ID obtained in step 3, call one of the following methods to return information on the details of a particular session: - - returns an array of objects for the supplied *SessionId*. - - returns a object with information for the specified *SessionId*. #### To monitor replication properties for all publications at a Distributor 1. Create a connection to the Distributor by using the class. 2. Create an instance of the class. 3. Set the property to the created in step 1. 4. Call the method to get the properties of the object. 5. Execute one or more of the following methods to return replication information for all Publishers that use this Distributor. - - returns a object that contains information about all Distribution Agents at this Distributor. - - returns a object that contains information about errors stored at the Distributor. - - returns a object that contains information about all Log Reader Agents at the Distributor. - - returns a object that contains information about all Merge Agents at the Distributor. - - returns a object that contains information about all other replication agents at the Distributor. - - returns a object that contains information about all Publishers at this Distributor. - - returns a object that returns the Publishers that use this Distributor. - - returns a object that contains information about all Queue Reader Agents at the Distributor. - - returns a object that contains details about the specified Queue Reader Agent and session. - - returns a object that contains session information about the specified Queue Reader Agent. - - returns a object that contains information about all Snapshot Agents at the Distributor. #### To monitor publication properties for a specific Publisher at the Distributor 1. Create a connection to the Distributor by using the class. 2. Get a object in one of these ways. - Create an instance of the class. Set the property for the Publisher, and set the property to the created in step 1. Call the method to get the properties of the object. If this method returns **false**, either the Publisher name was defined incorrectly or the publication does not exist. - From the accessed by means of the property of an existing object. 3. Execute one or more of the following methods to return replication information for all publications that belong to this Publisher. - - returns a object that contains details about the specified Distribution Agent and session. - - returns a object that contains session information about the specified Distribution Agent. - - returns a object that contains error record information about the specified error. - - returns a object that contains details for the specified Log Reader Agent and session. - - returns a object that contains session information for the specified Log Reader Agent. - - returns a object that contains details about the specified Merge Agent and session. - - returns a object that contains additional details about the specified Merge Agent and session. - - returns a object that contains session information for the specified Merge Agent. - - returns a object that contains additional session information for the specified Merge Agent. - - returns a object that contains information about all publications at this Distributor. - - returns a object that contains additional information about all publications at this Distributor. - - returns a object that contains details about the specified Snapshot Agent and session. - - returns a object that contains session information for the specified Snapshot Agent. - - returns a object that contains information about all subscriptions to publications at this Distributor. #### To monitor properties for a specific publication at the Distributor 1. Create a connection to the Distributor by using the class. 2. Get a object in one of these ways. - Create an instance of the class. Set the , , , and properties for the publication, and set the property to the created in step 1. Call the method to get the properties of the object. If this method returns **false**, either the publication properties were defined incorrectly or the publication does not exist. - From the accessed by means of the property of an existing object. 3. Execute one or more of the following methods to return information about this publication. - - returns a object that contains error records about the specified error. - - returns a object that contains information about the Log Reader Agent for this publication. - - returns a object that contains information about the monitor warning thresholds set for this publication. - - returns a object that contains information about the Queue Reader Agent used by this publication. - - returns a object that contains information about the Snapshot Agent for this publication. - - returns a object that contains information about subscriptions to this publication. - - returns a object that contains additional information about subscriptions to this publication based on the supplied . - - returns a object that contains latency information for the specified tracer token. - - returns a object that contains information about all tracer tokens inserted into this publication. #### To monitor transactional commands that are waiting to be applied at the Subscriber 1. Create a connection to the Distributor by using the class. 2. Get a object in one of these ways. - Create an instance of the class. Set the , , , and properties for the publication, and set the property to the created in step 1. Call the method to get the properties of the object. If this method returns **false**, either the publication properties were defined incorrectly or the publication does not exist. - From the accessed by means of the property of an existing object. 3. Execute the method, which returns a object. 4. Use the properties of this object to determine the estimated number of pending commands and the length of time it will take to complete the delivery of these commands. #### To set the monitor warning thresholds for a publication 1. Create a connection to the Distributor by using the class. 2. Get a object in one of these ways. - Create an instance of the class. Set the , , , and properties for the publication, and set the property to the created in step 1. Call the method to get the properties of the object. If this method returns **false**, either the publication properties were defined incorrectly or the publication does not exist. - From the accessed by means of the property of an existing object. 3. Execute the method. Note the current threshold settings in the returned of objects. 4. Execute the method. Pass the following parameters: - *metricID* - an value that represents the monitoring threshold metric from the following table: |Value|Description| |-----------|-----------------| |1|**expiration** - monitors for imminent expiration of subscriptions to transactional publications.| |2|**latency** - monitors for the performance of subscriptions to transactional publications.| |4|**mergeexpiration** - monitors for imminent expiration of subscriptions to merge publications.| |5|**mergeslowrunduration** - monitors the duration of merge synchronizations over low-bandwidth (dialup) connections.| |6|**mergefastrunduration** - monitors the duration of merge synchronizations over high-bandwidth (LAN) connections.| |7|**mergefastrunspeed** - monitors the synchronization rate of merge synchronizations over high-bandwidth (LAN) connections.| |8|**mergeslowrunspeed** - monitors the synchronization rate of merge synchronizations over low-bandwidth (dialup) connections.| - *enable* - value that indicates whether the metric is enabled for the publication. - *thresholdValue* - integer value that sets the threshold. - *shouldAlert* - integer that indicates whether this threshold should generate an alert.