some of the tables inside my distribution database are very intensely populated as you can see:
this was caused by adding a few large tables to the publication a couple of days ago
the cleaning job is running regularly:
Inside that job the following command is run:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
it looks like it is not doing the cleaning as expected.
what alternatives do I have?
I found a great article that helped me to solve the problem.
Here is the article:
What Immediate_sync means in Transactional Replication
the author is:
José Moreira Neto | Microsoft SQL Server Escalation Services
I will copy parts of this article in here because sometimes links become invalid on the internet.
Immediate_sync feature instructs Replication to maintain Snapshot BCP files and distributed transactions in the Distribution database should a new subscriber be created (or reinit) within the Retention period. By default, this information is purged as soon as existing subscribers receive the transactions. By keeping the Snapshot and transactions for hours or days, new subscribers can 1) Use old snapshot files, and 2) apply all pending changes since the snapshot. New Subscribers (or reinit) would not need to have a “fresh” snapshot generated.
IF TRUE (keep commands CACHED in Distribution database) If true, the synchronization files are created or re-created each time the Snapshot Agent runs. Subscribers are able to get the synchronization files immediately if the Snapshot Agent has completed before the subscription is created. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. Subscribers would then receive any “pending” transactions from the Distribution database.
If the publication has the property @immediate_sync set to “true” after the Snapshot Agent is created, transactions on the distribution database are maintained only to support the existing subscribers. In other words, at the time of the Snapshot generation completes all subscriptions to be initialized must be known. If a new subscriber is added, or marked for re-initialization after the Snapshot is Generated another snapshot is required.
IF FALSE (clean up CACHED commands as soon as Replicated) If false, the synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files until the Snapshot Agents are started and completed.
With immediate_sync was set to “false”, if you then mark ALL subscriptions for re-initialization, they can all apply the same Snapshot.
Special Considerations
If true, The Distribution Cleanup Agent will not purge the Distribution database or the Snapshot folder until the Publication Retention Period. This may result in unexpected distribution database growth.
When set false, and reinit a subscriber, a new snapshot is required, even if a snapshot was recently run. For example:
Create Publication with Immediate_sync = True
Create Snapshot 1
Create Sub A
Create Sub B
Replicate Transactions
Weeks later Reinit Sub A.
Create New Snapshot 2 for Sub A Reinit as Snapshot 1 has expired (outside retention period)
Set Immediate_sync = False
Reinit Sub B
As soon as Immediate_sync = False, the previous Snapshot files can no longer be used to reinit subscribers. Sub B will require a new snapshot to be generated.
After I changed the Immediate_Sync to false as describe here, all started to work as normal.
Also as described in this answer here:
Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.