2

How can I configure the settings on this screen in Replication Monitor using TSQL? Replication Monitor - Warnings page For reference, we're on SQL 2014.

2

Detailed information about programmatically monitoring replications can be found on the official Microsoft Docs site:

Reference: Programmatically Monitor Replication

There is detailed information on all the stored procedures required to query monitoring details:

To monitor Publishers, publications, and subscriptions from the Distributor

To monitor transactional commands waiting to be applied at the Subscriber

To monitor merge changes waiting to be uploaded or downloaded

  • At the Publisher: sp_showpendingchanges

    returns a result set showing information on changes that are waiting to be replicated to Subscribers

  • At the Subscriber: sp_showpendingchanges

    returns a result set showing information on changes that are waiting to be replicated to the Publisher

To monitor Merge Agent sessions

Answering your question

The stored procedures required to read or change the monitoring thresholds are the sp_replmontirohelppublicationthresholds and sp_replmonitorchangepublicationthreshold procedures.

To view the monitor threshold metrics for a publication

sp_replmonitorhelppublicationthresholds 
          [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'   
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'  

The parameters are:

@publisher : the name of the publisher
@publisher_db : the name of the published database
@pbulication : the name of the publication
@publication_type : 0 = transactional publication
................ 1 = snapshot publication
................ 2 = merge publication

To modify the monitor threshold metrics for a publication

sp_replmonitorchangepublicationthreshold 
          [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'   
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @metric_id = ] metric_id ]   
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'   
    [ , [ @value = ] value ]   
    [ , [ @shouldalert = ] shouldalert ]   
    [ , [ @mode = ] mode ]  

The parameters are:

@publisher : the name of the publisher
@publisher_db : the name of the published database
@pbulication : the name of the publication
@publication_type : 0 = transactional publication
................ 1 = snapshot publication
................ 2 = merge publication
@metric_id : 1 = expiration
................ 2 = latency
................ 4 = mergeexpiration
................ 5 = mergeslowrunduration
................ 6 = mergefastrunduration
................ 7 = mergefastrunspeed
................ 8 = mergeslowrunspeed
@thresholdmetricname: the name of the publication threshold
................ (either this parameter or @metric_id is required)
@value : the new metric value
@shouldalert : generate alert (1=yes;0=no)
@mode : monitoring mode (1=on;2=off)

2
  • Wow - champion answer! I'll mark it as accepted after I get a chance to run the scripts at work tomororw. Cheers! – Mike Aug 15 '17 at 8:33
  • 1
    Yep, works beautifully. Thanks for the comprehensive answer addressing both my specific question and the larger question of programmatic monitoring. Cheers! – Mike Aug 15 '17 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.