Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Symptoms

The sp_replmonitorsubscriptionpendingcmds stored procedure is not compatible with peer-to-peer (P2P) replication. It returns the incorrect number of pending commands. After you apply this fix, sp_replmonitorsubscriptionpendingcmds will be compatible with P2P replication.

Note The "Undistributed Commands" tab of a Replication Monitor invokes sp_replmonitorsubscriptionpendingcmds behind the scenes.

Resolution

Cumulative update information

This problem is fixed in the following cumulative update for SQL Server:

Cumulative Update 17 for SQL Server 2019

Note After you apply this cumulative update, sp_replmonitorsubscriptionpendingcmds might still report an incorrect number of pending commands for P2P replication if the MSrepl_originators table contains stale entries.

If the MSrepl_originators table contains stale entries, running sp_replmonitorsubscriptionpendingcmds will generate the following warning message in the SQL Server error log:

Warning: Unable to get database version for the subscription database '<database_name>'. The sp_replmonitorsubscriptionpendingcmdsmay report the incorrect number of pending commands for P2P replication.

To resolve this problem, you either delete the stale entries from MSrepl_originators or pass the correct "dbversion" of a subscription database as a subdb_version argument when you run sp_replmonitorsubscriptionpendingcmds.

Tips: You can select either of the following methods to find the "dbversion" of a subscription database:

  • Run the following query on a distribution database:                                                                                        select dbversion from MSrepl_originators
    where publisher_database_id = <Publisher_Database_ID>
    and srvname = <Subscriber_Server>
    and dbname = <Subscriber_Database>
    Note You need to substitute appropriate values for <Publisher_Database_ID> , <Subscriber_Server> and <Subscriber_Database>.

  • Run the following query on a subscription database:                                                                                    declare @current_version int , @currentGuid uniqueidentifier
    select @currentGuid = recovery_fork_guid 
    from sys.database_recovery_status
    where database_id = db_id()
    select @current_version = substring(convert(binary(16), isnull(@currentGuid, 0x0)),1,6)
    print @current_version

Each new cumulative update for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for your version of SQL Server:

Latest cumulative update for SQL Server 2019

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.

Need more help?

Expand your skills

EXPLORE TRAINING >

Get new features first

JOIN MICROSOFT 365 INSIDERS >

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×