| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_replmonitorsubscriptionpendingcmds (T-SQL) |
Describes the sp_replmonitorsubscriptionpendingcmds stored procedure that returns information on the number of pending commands for a subscription to a transactional publication. |
markingmyname |
maghan |
randolphwest |
08/22/2023 |
sql |
replication |
reference |
|
|
|
[!INCLUDE SQL Server SQL MI]
Returns information on the number of pending commands for a subscription to a transactional publication and a rough estimate of how much time it takes to process them. This stored procedure 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.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_replmonitorsubscriptionpendingcmds [ @publisher = ] 'publisher'
, [ @publisher_db = ] 'publisher_db'
, [ @publication = ] 'publication'
, [ @subscriber = ] 'subscriber'
, [ @subscriber_db = ] 'subscriber_db'
, [ @subscription_type = ] subscription_type
, [ @subdb_version = ] subdb_version
The name of the Publisher. publisher is sysname, with no default.
The name of the published database. publisher_db is sysname, with no default.
The name of the publication. publication is sysname, with no default.
The name of the Subscriber. subscriber is sysname, with no default.
The name of the subscription database. subscriber_db is sysname, with no default.
The type of subscription. subscription_type is int, with no default and can be one of these values.
| Value | Description |
|---|---|
0 |
Push subscription |
1 |
Pull subscription |
The dbversion of the subscription database. subdb_version is an optional parameter of type int, with a default value of 0.
| Column name | Data type | Description |
|---|---|---|
pendingcmdcount |
int | The number of commands that are pending for the subscription. |
estimatedprocesstime |
int | Estimate of the number of seconds required to deliver all of the pending commands to the subscriber. |
0 (success) or 1 (failure).
sp_replmonitorsubscriptionpendingcmds is used with transactional replication.
Prior to [!INCLUDE sssql19-md] CU17, sp_replmonitorsubscriptionpendingcmds wasn't supported with peer-to-peer replication, and returned an incorrect number of pending commands when used to query peer-to-peer replication topology. In [!INCLUDE sssql19-md] CU 17, support was added to make sp_replmonitorsubscriptionpendingcmds compatible with peer-to-peer publications.
However, even with [!INCLUDE sssql19-md] CU17 or later, sp_replmonitorsubscriptionpendingcmds could report an incorrect number of pending commands when used with peer-to-peer replication if the table MSrepl_originators contains a stale entry of an incorrect version of the subscription database. To correct the problem, either delete all the stale entries from MSrepl_originators or pass the correct dbversion of the subscription database when using the subdb_version argument for the sp_replmonitorsubscriptionpendingcmds stored procedure.
See KB5017009 for details on how to determine dbversion.
Only members of the sysadmin fixed server role at the Distributor or members of the db_owner fixed database role in the distribution database can execute sp_replmonitorsubscriptionpendingcmds. Members of the publication access list for a publication that uses the distribution database can execute sp_replmonitorsubscriptionpendingcmds to return pending commands for that publication.