Sp_replmonitorhelpsubscription (this stored procedure is executed on the distributor to monitor replication)

Homepage technical resource library learning download support community forum sp_replmonitorhelpsubscription (TRANSACT-SQL)

Returns the current status of one or more subscriptions on the Publishing Server, and returns a row for each returned subscription. Execute this stored procedure on the distribution database on the distribution server to monitor replication.

Transact-SQL syntax conventions

Syntax

sp_replmonitorhelpsubscription [ @publisher = ] 'publisher'    [ , [ @publisher_db = ] 'publisher_db' ]    [ , [ @publication = ] 'publication' ]    [ , [ @publication_type = ] publication_type ]     [ , [ @mode = ] mode ]    [ , [ @topnum = ] topnum ]     [ , [ @exclude_anonymous = ] exclude_anonymous ]     [ , [ @refreshpolicy = ] refreshpolicy ]

Parameters

[ @ Publisher=] 'Publisher '

The name of the publishing server that is monitoring its status. The data type of publisher isSysnameThe default value is null.
IfNullReturns the information of all the publishing servers that use the distributor.

[ @ Publisher_db=] 'Publisher_db '

The name of the published database. The data type of publisher_db isSysnameThe default value is null.
If it is null, information about all published databases on the Publishing Server is returned.

[ @ Publication=] 'Publication '

The name of the release to be monitored. The publication data type isSysnameThe default value is null.

[ @ Publication_type=] Publication_type

Release type. The publication_type data type isIntWhich can be one of the following values.

Value	Description
0	Publish transactions.
1	Publish a snapshot.
2	Merge and publish.
NULL (default)	Replication is used to determine the release type.

[ @ Mode=] Mode

The filtering mode used to return subscription monitoring information. The data type of mode isIntWhich can be one of the following values.

Value	Description
0(Default)	Returns All subscriptions.
1	Only subscriptions with errors are returned.
2	Only subscriptions that have generated alarms when the threshold is reached are returned.
3	Only subscriptions with errors or warnings generated when the threshold is reached are returned.
4	Returns the 25 most frequently executed subscriptions.
5	Returns the 50 subscriptions with the worst execution.
6	Only the current synchronization subscription is returned.
7	Only the subscription that is not synchronized is returned.

[ @ Topnum=] Topnum

Limit the result set to the specified subscription number at the top of the returned data. The data type of topnum isInt, No default value.

[ @ Exclude_anonymous=] Exclude_anonymous

Indicates whether to exclude anonymous request subscriptions from the result set. The data type of exclude_anonymous is bit, and the default value is
0; Value:1Indicates that anonymous subscription is excluded. The value is0Indicates that anonymous subscription is included.

[ @ Refreshpolicy =] Refreshpolicy

For internal use only.

Result set

Column name	Data Type	Description
Status	Int	The maximum status of all replication agents associated with the release. It can be one of the following values: 1= Started 2= Successful 3= In progress 4= Idle 5= Retry... 6= Failed
Warning	Int	The maximum threshold warning generated by the published subscription may be the result of one or more of the following values for logic or operation. 1= Expiration-transaction publishing has not been synchronized within the retention period threshold. 2= Latency-the time taken to copy data from the transaction Publishing Server to the subscription server exceeds the threshold (in seconds ). 4= Mergeexpiration-the subscription of merged publishing has not been synchronized within the retention period threshold. 8= Mergefastrunduration-the time it takes to synchronize the merged subscriptions exceeds the threshold (in seconds) required by the quick network connection ). 16= Mergeslowrunduration-the time used to synchronize the merged subscription exceeds the threshold (in seconds) required by the slow or dial-up network connection ). 32= Mergefastrunspeed-the row transfer rate during the synchronization process of the merged subscription does not maintain the threshold rate on the fast network connection (in the unit of the number of lines transferred per second ). 64= Mergeslowrunspeed-the row transmission rate during the synchronization process of the merged subscription does not maintain the slow speed or the threshold rate of the dial-up network connection (in the unit of the number of lines transferred per second ).
Subscriber	Sysname	The name of the subscription server.
Subscriber_db	Sysname	The name of the database to be subscribed.
Publisher_db	Sysname	The name of the published database.
Publication	Sysname	The name of the release.
Publication_type	Int	Release type, which can be one of the following values: 0= Transaction Publishing 1= Snapshot Publishing 2= Merge and publish
Subtype	Int	Subscription type, which can be one of the following values: 0= Push 1= Request 2= Anonymous
Latency	Int	The maximum latency (in seconds) of data changes that are propagated by the Log Reader Agent or distribution agent during transaction Publishing ).
Latencythreshold	Int	The longest delay of transaction Publishing. A warning is generated when the delay is higher than this time.
Agentnotrunning	Int	The length of time when the agent is not running, in hours.
Agentnotrunningthreshold	Int	The length of time before the proxy is run, in hours.
Timetoexpiration	Int	The length of time before the subscription expires when the subscription is not synchronized, in hours.
Expirationthreshold	Int	The time before the warning is generated due to subscription expiration, in hours.
Last_distsync	Datetime	The date and time when the distribution agent was last run.
Distribution_agentname	Sysname	The name of the distribution agent job to which the transaction is published and subscribed.
Mergeagentname	Sysname	Name of the Merge Agent job for merging, publishing, and subscription.
Mergesubscriptionfriendlyname	Sysname	The friendly name specified for the subscription.
Mergeagentlocation	Sysname	The name of the server that runs the merge proxy.
Mergeconnectiontype	Int	The connection used to synchronize the subscription to the merged publishing can be one of the following values: 1= LAN) 2= Dial-up network connection 3= Web synchronization.
Mergeperformance	Int	The performance of the last synchronization of the subscription relative to all of its syncs is divided by the transfer rate of the last synchronization by the average value of the previous transfer rate.
Mergerunspeed	Float	The last synchronization transfer rate of the subscription.
Mergerunduration	Int	The last synchronization time of the subscription.
Monitorranking	Int	The ranking value used to sort subscriptions in the result set. It can be one of the following values: For transaction release: 60= Error 56= Warning: performance in "severe" status 52= Warning: it is about to expire or has expired 50= Warning: subscription is not initialized 40= Retry failed command 30= Not running (succeeded) 20= Running (starting, running, or idle) For merged publishing: 60= Error 56= Warning: performance in "severe" status 54= Warning: Merge of long-running tasks 52= Warning: about to expire 50= Warning: subscription is not initialized 40= Retry failed command 30= Running (starting, running, or idle) 20= Not running (succeeded)
Distributionagentjobid	Binary (16)	ID of the distribution agent job for transaction publishing and subscription.
Mergeagentjobid	Binary (16)	ID of the Merge Agent job subscribed to for merging and publishing.
Distributionagentid	Int	ID of the subscribed distribution agent job.
Distributionagentprofileid	Int	The ID of the proxy configuration file used by the Distribution agent.
Mergeagentid	Int	ID of the merged proxy job subscribed.
Mergeagentprofileid	Int	The ID of the proxy configuration file used by the merge proxy.

Return code value

0(Successful) or1(Failed)

Note

Sp_replmonitorhelpsubscriptionUsed for all types of replication.

Sp_replmonitorhelpsubscriptionSort the result set based on the severity of the subscription status. The severity is determined by the monitorranking value.
For example, all subscribed rows in the error state are placed above the subscribed rows in the warning state.

Permission

OnlyDb_ownerOrReplmonitorMembers of the fixed database role can only executeSp_replmonitorhelpsubscription.

See

Concept monitors replication programmatically

------- Eg:
Sp_replmonitorhelpsubthers @ publisher = 'db-distothers ', @ publisher_db = 'scm', @ publication = 'orderform', @ publication_type = 0