TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_replmonitorhelpsubscription (Transact-SQL)

 

Applies To: SQL Server

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.

Topic link icon Transact-SQL Syntax Conventions

  
sp_replmonitorhelpsubscription [ @publisher = ] 'publisher'  
    [ , [ @publisher_db = ] 'publisher_db' ]  
    [ , [ @publication = ] 'publication' ]  
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @mode = ] mode ]  
    [ , [ @topnum = ] topnum ]   
    [ , [ @exclude_anonymous = ] exclude_anonymous ]   
    [ , [ @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 is 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 tries to determine the publication type.

[ @mode = ] mode
Is the filtering mode to use when returning subscription monitoring information. mode is int, and can be one of these values.

ValueDescription
0 (default)Returns all subscriptions.
1Returns only subscriptions with errors.
2Returns only subscriptions that have generated threshold metric warnings.
3Returns only subscriptions that either have errors or have generated threshold metric warnings.
4Returns the top 25 worst performing subscriptions.
5Returns the top 50 worst performing subscriptions.
6Returns only subscriptions that are currently being synchronized.
7Returns only subscriptions that are not 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 no default.

[ @exclude_anonymous = ] exclude_anonymous
Is 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 and a value of 0 means that they are included.

[ @refreshpolicy= ] refreshpolicy
Internal use only.

Column nameData typeDescription
statusintExamines 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
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.
subscribersysnameIs the name of the Subscriber.
subscriber_dbsysnameIs the name of the database used for the subscription.
publisher_dbsysnameIs the name of the publication database.
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
subtypeintIs the subscription type, which can be one of the following values:

 0 = Push

 1 = Pull

 2 = Anonymous
latencyintThe highest latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional publication.
latencythresholdintIs the maximum latency for the transactional publication above which a warning is raised.
agentnotrunningintIs the length of time, in hours, during which the agent has not run.
agentnotrunningthresholdintIs the length of time, in hours, that the agent has not run before a warning is raised.
timetoexpirationintIs the length of time, in hours, before the subscription expires if not synchronized.
expirationthresholdintIs the time, in hours, before the subscription expires that a warning is raised.
last_distsyncdatetimeIs the datetime that the Distribution Agent last ran.
distribution_agentnamesysnameIs the name of the Distribution Agent job for the subscription to a transactional publication.
mergeagentnamesysnameIs the name of the Merge Agent job for the subscription to a merge publication.
mergesubscriptionfriendlynamesysnameIs the friendly name given to the subscription.
mergeagentlocationsysnameIs the name of the server on which the Merge Agent runs.
mergeconnectiontypeintConnection 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.
mergePerformanceintPerformance 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.
mergerunspeedfloatIs the delivery rate of the last synchronization for the subscription.
mergerundurationintIs the length of time to complete the last synchronization of the subscription.
monitorrankingintIs 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)
distributionagentjobidbinary(16)ID of the Distribution Agent job for subscriptions to a transactional publication.
mergeagentjobidbinary(16)ID of the Merge Agent job for subscriptions to a merge publication.
distributionagentidintID of the Distribution Agent job for the subscription.
distributionagentprofileidintID of the agent profile used by the Distribution Agent.
mergeagentidintID of the Merge Agent job for the subscription.
mergeagentprofileidintID of the agent profile used by the Merge Agent.

0 (success) or 1 (failure)

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.

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

Programmatically Monitor Replication

Community Additions

ADD
Show:
© 2016 Microsoft