distribution cleanup agent history message meaning.

Question

hello,
i have a transaction replication setup. I want to know what is the meaning of below message that is returned by Distribution clean up job history.
For example in following message if its deleting 6rows and 1row each from _command and _transaction table then what is the meaning of "Removed 9844 replicated transactions consisting of 27385 statements"

Deleted 6 row(s) per millisecond from MSrepl_commands [SQLSTATE 01000] (Message 22121)
Deleted 1 row(s) per millisecond from MSrepl_transactions [SQLSTATE 01000] (Message 22121)
Removed 9844 replicated transactions consisting of 27385 statements in 12800 milliseconds (2 rows/millisec). [SQLSTATE 01000] (Message 21010).

Answer 1

Hi @SQLServerBro,

The distribution database is a cach of undistributed commands that the Distribution SQL Agent jobs are responsible to deliver to their subscribers. The primary tables holding the cache of undistributed data is MSrepl_transactions and MSrepl_commands. The distribution tables required purging of commands already delivered to subscribers
This is a summary message that tells you how much was cleared

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@YufeiShao-msft how do i know from these tables which commands are delivered to the subscribers and which commands are not delivered?

Answer 3

See:
https://www.mssqltips.com/sqlservertip/2590/determine-data-latency-between-publisher-and-subscriber-in-sql-server-transactional-replication/

Answer 4

Hi @SQLServerBro，

Check out these articles:
The Undistributed Commands, displays information about the number of commands in the distribution database that have not been delivered to the selected :
https://learn.microsoft.com/en-us/sql/relational-databases/replication/subscription-undistributed-commands-transactional-subscription?view=sql-server-ver15

View Replicated Commands and Information in Distribution Database, pending commands in the distribution database can be viewed programmatically using replication stored procedures:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/view-replicated-commands-and-information-in-distribution-database?view=sql-server-ver15

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.