Edit

sp_replmonitorhelppublication (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

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.

Transact-SQL syntax conventions

Syntax

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

Arguments

[ @publisher = ] N'publisher'

The name of the Publisher the status of which is being monitored. @publisher is sysname, with a default of NULL. If NULL, information is returned for all Publishers that use the Distributor.

[ @publisher_db = ] N'publisher_db'

The name of the published database. @publisher_db is sysname, with a default of NULL. If NULL, then information is returned for all published databases at the Publisher.

[ @publication = ] N'publication'

The name of the publication being monitored. @publication is sysname, with a default of NULL.

[ @publication_type = ] publication_type

The type of publication. @publication_type is int, and can be one of these values.

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

[ @refreshpolicy = ] refreshpolicy

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Result set

Column name Data type Description
publisher_db sysname The name of the Publisher.
publication sysname The name of a publication.
publication_type int The type of publication, and can be one of these values.

0 = Transactional publication

1 = Snapshot publication

2 = Merge publication
status int Maximum status of all replication agents associated with the publication, and can be one of these values.

1 = Started

2 = Succeeded

3 = In progress

4 = Idle

5 = Retrying

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

1 = expiration - a subscription to a transactional publication hasn't 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 hasn't 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 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 network connection.
worst_latency int The highest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
best_latency int The lowest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
average_latency int The average latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
last_distsync datetime The last datetime that the Distribution Agent ran.
retention int The retention period for the publication.
latencythreshold int The latency threshold set for the transactional publication.
expirationthreshold int The expiration threshold set for the publication if it's a merge publication.
agentnotrunningthreshold int The threshold set for the longest time for an agent not to have run.
subscriptioncount int The number of subscriptions to a publication.
runningdistagentcount int The number of distribution agents running for the publication
snapshot_agentname sysname The name of the Snapshot Agent job for the publication.
logreader_agentname sysname The name of the Log Reader Agent job for the transactional publication.
qreader_agentname sysname The name of the Queue Reader Agent job for a transactional publication that supports queued updating.
worst_runspeedPerf int The longest synchronization time for the merge publication.
best_runspeedPerf int The shortest synchronization time for the merge publication.
average_runspeedPerf int The average synchronization time for the merge publication.
retention_period_unit int The unit used to express retention.
publisher sysname The name of the instance of SQL Server publishing the publication.

Return code values

0 (success) or 1 (failure).

Remarks

sp_replmonitorhelppublication is used with all types of replication.

Permissions

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

Related content