sp_replmonitorhelppublication (Transact-SQL)

Artikel
2024-08-23

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.

Programmatically Monitor Replication

Dela via