“The Only Thing That Is Constant Is Change”

Steps to aggressively purge replicated transactions from the Distribution database.

Posted by database-wiki on April 11, 2016


Distribution database cleanup agent is not able to purge already replication rows from the Distribution database faster then rows are being added. As result, the distribution database is growing in size.


To reduce locking contention, the cleanup agent deletes in batches of 2000 and 5000. When a large batch of transaction have been replicated the Distribution cleanup agent may not be able to deletes fast enough to keep up with new rows being added. You may notice the the number of rows in the replication tracking tables growing to over 10+ million.

select count(*) from distribution..msrepl_commands

select count(*) from distribution..msrepl_transactions


Distribution Database Cleanup.

Use these steps to aggressively clean-out already replicated data from the Distribution database. These steps will save out all rows since the last replicated transaction from msrepl_transactions and msrepl_commands to temp tables, truncate msrepl_transactions and msrepl_commands, and then re-insert the saved rows. We determine which rows to save by checking the transaction_timestamp value in the MSreplication_subscriptions table at each subscriber for each subscription database..

Reminder –this should work, but be prepared to have to re-initialize the subscriber.

***Steps for Manual Truncation of Distribution Database***

1) Stop all logreader and distribution agents utilizing this distribution server. Use the queries below to identify all Publisher and Subscriber servers.

–returns list of publishers using a distributor


–returns list of published databases


FROM [distribution].[dbo].[MSpublisher_databases]

–Run in each published database

–Returns publication/article/subscriber information


2) At each subscriber and in each subscriber database, query the msreplication_subscriptions table for the bookmark xactid – you should find only one row per publication in this table at each subscriber. This value indicates what rows have already been delivered to each subscriber.

select transaction_timestamp from msreplication_subscriptions

Here is the value returned for me: 0x00000045000002560003000000000000

3) Determine SMALLEST value of ALL subscribers in ALL subscription databases. Use that value when querying the msrepl_transactions and msrepl_commands tables in the distribution database for rows to preserve. Now, let’s query msrepl_transaction and msrepl_commands, using our transaction_timestamp and see if there are any other commands that have not been propagated to either subscriber

–Be sure to use your smaller transaction_timestamp value here:

select count(*) from distribution..msrepl_commands where xact_seqno>=0x00000045000002560003000000000000

select count(*) from distribution..msrepl_transactions where xact_seqno>=0x00000045000002560003000000000000

4) How many rows do you get back? If around 1 million rows proceed to save those rows. If more, determine which distribution agent is behind (lowest LSN) and see what steps can be used to move that Distribution Agent forward.

5) Save rows returned by the above queries to temp tables before we truncate the Replication tables:

Begin Transaction

select * into distribution..msrepl_commands_temp from distribution..msrepl_commands

where xact_seqno>=0x00000045000002560003000000000000

select * into distribution..msrepl_transactions_temp from distribution..msrepl_transactions

where xact_seqno>=0x00000045000002560003000000000000

–did you get same row counts as in step 3), if yes, then commit the transaction

Commit Transaction

6) To verify you can query the temp tables and make sure that the rows were selected out. Then, perform the following to truncate the two tables in the distribution database:

truncate table distribution..msrepl_commands

truncate table distribution..msrepl_transactions

7) Once these are emptied, insert our rows back in from our temp tables:

insert into distribution..msrepl_commands

select * from distribution..msrepl_commands_temp

insert into distribution..msrepl_transactions

select * from distribution..msrepl_transactions_temp

Now, verify the rows are successfully re-inserted into the two tables. Then start the logreader and distribution agents back up and verify that they are able to successfully synchronize to both subscribers.

One Response to “Steps to aggressively purge replicated transactions from the Distribution database.”

  1. Does this really work? Somebody did it?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: