distribution cleanup agent history message meaning.

Heisenberg 261 Reputation points
2022-04-19T18:25:04.99+00:00

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).

SQL Server | Other
{count} votes

4 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,146 Reputation points
    2022-04-20T06:26:27.447+00:00

    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.

    0 comments No comments

  2. Heisenberg 261 Reputation points
    2022-04-20T15:34:13.32+00:00

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

    0 comments No comments

  3. Tom Phillips 17,771 Reputation points
    2022-04-20T19:54:57.857+00:00
    0 comments No comments

  4. YufeiShao-msft 7,146 Reputation points
    2022-04-21T01:47:47.733+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.