MSSQL Replication Monitor – Set Warnings and Thresholds Using TSQL

replicationsql servert-sql

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.

Best Answer

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)

Related Question