sp_replmonitorhelpsubscription (Transact-SQL)

บทความ
08/23/2024

Applies to: SQL Server Azure SQL Managed Instance

Returns current status information for subscriptions belonging to one or more publications at the Publisher and returns one row for each returned subscription. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.

Transact-SQL syntax conventions

Syntax

sp_replmonitorhelpsubscription
    [ [ @publisher = ] N'publisher' ]
    [ , [ @publisher_db = ] N'publisher_db' ]
    [ , [ @publication = ] N'publication' ]
    [ , [ @publication_type = ] publication_type ]
    [ , [ @mode = ] mode ]
    [ , [ @topnum = ] topnum ]
    [ , [ @exclude_anonymous = ] exclude_anonymous ]
    [ , [ @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

If 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 tries to determine the publication type.

[ @mode = ] mode

The filtering mode to use when returning subscription monitoring information. @mode is int, and can be one of these values.

Value	Description
`0` (default)	Returns all subscriptions.
`1`	Returns only subscriptions with errors.
`2`	Returns only subscriptions that generate threshold metric warnings.
`3`	Returns only subscriptions that either have errors, or generate threshold metric warnings.
`4`	Returns the top 25 worst performing subscriptions.
`5`	Returns the top 50 worst performing subscriptions.
`6`	Returns only subscriptions that are currently being synchronized.
`7`	Returns only subscriptions that aren't currently being synchronized.

[ @topnum = ] topnum

Restricts the result set to only the specified number of subscriptions at the top of the returned data. @topnum is int, with a default of NULL.

[ @exclude_anonymous = ] exclude_anonymous

If anonymous pull subscriptions are excluded from the result set. @exclude_anonymous is bit, with a default of 0.

A value of 1 means that anonymous subscriptions are excluded.
A value of 0 means that they're included.

[ @refreshpolicy = ] refreshpolicy

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

Result set

Column name	Data type	Description
`status`	int	Examines the status of all the replication agents associated with the publication, and returns the highest status found in the following order: `6` = Failed `5` = Retrying `2` = Stopped `4` = Idle `3` = In progress `1` = Started
`warning`	int	Maximum 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 hasn't 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 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 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 failed to maintain the threshold rate, in rows per second, over a slow network connection.
`subscriber`	sysname	The name of the Subscriber.
`subscriber_db`	sysname	The name of the database used for the subscription.
`publisher_db`	sysname	The name of the publication database.
`publication`	sysname	The name of a publication.
`publication_type`	int	The type of publication, which can be one of these values: `0` = Transactional publication `1` = Snapshot publication `2` = Merge publication
`subtype`	int	The subscription type, which can be one of the following values: `0` = Push `1` = Pull `2` = Anonymous
`latency`	int	The highest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
`latencythreshold`	int	The maximum latency for the transactional publication, above which a warning is raised.
`agentnotrunning`	int	The length of time, in hours, during which the agent hasn't run.
`agentnotrunningthreshold`	int	The length of time, in hours, that the agent hasn't run before a warning is raised.
`timetoexpiration`	int	The length of time, in hours, before the subscription expires if not synchronized.
`expirationthreshold`	int	The time, in hours, before the subscription expires that a warning is raised.
`last_distsync`	datetime	The datetime that the Distribution Agent last ran.
`distribution_agentname`	sysname	The name of the Distribution Agent job for the subscription to a transactional publication.
`mergeagentname`	sysname	The name of the Merge Agent job for the subscription to a merge publication.
`mergesubscriptionfriendlyname`	sysname	The friendly name given to the subscription.
`mergeagentlocation`	sysname	The name of the server on which the Merge Agent runs.
`mergeconnectiontype`	int	Connection used when synchronizing a subscription to a merge publication, which can be one of the following values: `1` = local area network (LAN) `2` = dial-up network connection `3` = Web synchronization.
`mergePerformance`	int	Performance of the last synchronization compared to all synchronizations for the subscription, which is based on the delivery rate of the last synchronization divided by the average of all previous delivery rates.
`mergerunspeed`	float	The delivery rate of the last synchronization for the subscription.
`mergerunduration`	int	The length of time to complete the last synchronization of the subscription.
`monitorranking`	int	The ranking value used to order the subscriptions in the result set, and can be one of these values: For a transactional publication: `60` = Error `56` = Warning: performance critical `52` = Warning: expiring soon or expired `50` = Warning: subscription uninitialized `40` = Retrying failed command `30` = Not running (success) `20` = Running (starting, running, or idle) For a merge publication: `60` = Error `56` = Warning: performance critical `54` = Warning: long-running merge `52` = Warning: expiring soon `50` = Warning: subscription uninitialized `40` = Retrying failed command `30` = Running (starting, running, or idle) `20` = Not running (success)
`distributionagentjobid`	binary(16)	ID of the Distribution Agent job for subscriptions to a transactional publication.
`mergeagentjobid`	binary(16)	ID of the Merge Agent job for subscriptions to a merge publication.
`distributionagentid`	int	ID of the Distribution Agent job for the subscription.
`distributionagentprofileid`	int	ID of the agent profile used by the Distribution Agent.
`mergeagentid`	int	ID of the Merge Agent job for the subscription.
`mergeagentprofileid`	int	ID of the agent profile used by the Merge Agent.

Return code values

0 (success) or 1 (failure).

Remarks

sp_replmonitorhelpsubscription is used with all types of replication.

sp_replmonitorhelpsubscription orders the result set based on the severity of the status of the subscription, which is determined by the value of monitorranking. For example, rows for all subscriptions in an error state are ordered above rows for subscriptions in a warning state.

Permissions

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

Programmatically Monitor Replication

แชร์ผ่าน

sp_replmonitorhelpsubscription (Transact-SQL)

Syntax

Arguments

[ @publisher = ] N'publisher'

[ @publisher_db = ] N'publisher_db'

[ @publication = ] N'publication'

[ @publication_type = ] publication_type

[ @mode = ] mode

[ @topnum = ] topnum

[ @exclude_anonymous = ] exclude_anonymous

[ @refreshpolicy = ] refreshpolicy

Result set

Return code values

Remarks

Permissions

คำติชม

แหล่งทรัพยากรเพิ่มเติม

แชร์ผ่าน

sp_replmonitorhelpsubscription (Transact-SQL)

Syntax

Arguments

[ @publisher = ] N'publisher'

[ @publisher_db = ] N'publisher_db'

[ @publication = ] N'publication'

[ @publication_type = ] publication_type

[ @mode = ] mode

[ @topnum = ] topnum

[ @exclude_anonymous = ] exclude_anonymous

[ @refreshpolicy = ] refreshpolicy

Result set

Return code values

Remarks

Permissions

Related content

คำติชม

แหล่งทรัพยากรเพิ่มเติม