sp_replmonitorhelppublication (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_replmonitorhelppublication (Transact-SQL)

 

Applies To: SQL Server

Returns current status information for one or more publications at a Publisher. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.

Topic link icon Transact-SQL Syntax Conventions


sp_replmonitorhelppublication [ @publisher = ] 'publisher'
    [ , [ @publisher_db = ] 'publisher_db' 
    [ , [ @publication = ] 'publication' 
    [ , [ @publication_type = ] publication_type ] 
    [ , [ @refreshpolicy = ] refreshpolicy ]

[ @publisher = ] 'publisher'
Is the name of the Publisher the status of which is being monitored. publisher is sysname, with a default value of NULL. If null, information will be returned for all Publishers that use the Distributor.

[ @publisher_db = ] 'publisher_db'
Is the name of the published database. publisher_db is sysname, with a default value of NULL. If NULL, then information is returned for all published databases at the Publisher.

[ @publication = ] 'publication'
Is the name of the publication being monitored. publication is sysname, with a default value of NULL.

[ @publication_type = ] publication_type
If the type of publication. publication_type is int, and can be one of these values.

ValueDescription
0Transactional publication.
1Snapshot publication.
2Merge publication.
NULL (default)Replication attempts to determine the publication type.

[ @refreshpolicy= ] refreshpolicy
Internal use only.

Column nameData typeDescription
publisher_dbsysnameIs the name of the Publisher.
publicationsysnameIs the name of a publication.
publication_typeintIs the type of publication, which can be one of these values.

 0 = Transactional publication

 1 = Snapshot publication

 2 = Merge publication
statusintMaximum status of all replication agents associated with the publication, which can be one of these values.

 1 = Started

 2 = Succeeded

 3 = In progress

 4 = Idle

 5 = Retrying

 6 = Failed
warningintMaximum threshold warning generated by a subscription belonging to the publication, which can be the logical OR result of one or more of these values.

 1 = expiration – a subscription to a transactional publication has not been synchronized within the retention period threshold.

 2 = latency - the time taken to replicate data from a transactional Publisher to the Subscriber exceeds the threshold, in seconds.

 4 = mergeexpiration - a subscription to a merge publication has not been synchronized within the retention period threshold.

 8 = mergefastrunduration - the time taken to complete synchronization of a merge subscription exceeds the threshold, in seconds, over a fast network connection.

 16 = mergeslowrunduration - the time taken to complete synchronization of a merge subscription exceeds the threshold, in seconds, over a slow or dial-up network connection.

 32 = mergefastrunspeed – the delivery rate for rows during synchronization of a merge subscription has failed to maintain the threshold rate, in rows per second, over a fast network connection.

 64 = mergeslowrunspeed – the delivery rate for rows during synchronization of a merge subscription has failed to maintain the threshold rate, in rows per second, over a slow or dial-up network connection.
worst_latencyintThe highest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
best_latencyintThe lowest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
average_latencyintThe average latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
last_distsyncdatetimeIs the last datetime that the Distribution Agent ran.
retentionintIs the retention period for the publication.
latencythresholdintIs the latency threshold set for the transactional publication.
expirationthresholdintIs the expiration threshold set for the publication if it is a merge publication.
agentnotrunningthresholdintIs the threshold set for the longest time for an agent not to have run.
subscriptioncountintIs the number of subscriptions to a publication.
runningdistagentcountintIs the number of distribution agents running for the publication
snapshot_agentnamesysnameName of the Snapshot Agent job for the publication.
logreader_agentnamesysnameName of the Log Reader Agent job for the transactional publication.
qreader_agentnamesysnameName of the Queue Reader Agent job for a transactional publication that supports queued updating.
worst_runspeedPerfintIs the longest synchronization time for the merge publication.
best_runspeedPerfintIs the shortest synchronization time for the merge publication.
average_runspeedPerfintIs the average synchronization time for the merge publication.
retention_period_unitintIs the unit used to express retention.
publishersysnameThe name of the instance of SQL Server publishing the publication.

0 (success) or 1 (failure)

sp_replmonitorhelppublication is used with all types of replication.

Only members of the db_owner or replmonitor fixed database role on the distribution database can execute sp_replmonitorhelppublication.

Programmatically Monitor Replication

Community Additions

ADD
Show:
© 2016 Microsoft