Snapshot Publication is not allowing cleanup of the distribution database RRS feed

  • Question

  • In my setup I have both Transactional publications and Snapshot publications. My understanding is that the Snapshot publication does not track or care about transactions that occured since last sync. Every execution of sync will deliver the existing snapshot, where the snapshotting operation simply takes all the data out of the tables.
    The problem is that the code in sp_MSmaximum_cleanup_seqno considers the last transaction delivered by each of the distribution agents found in MSdistribution_agents. That includes the distribution agent that delivers the snapshot. Therefore, my distribution database accumulates transactions between executions of the snapshot+sync operations.

    As I see it, could have missed some configuration option. Otherwise, it seems my only option is to modify sp_MSmaximum_cleanup_seqno or some similar circumvention of the tracking that occurs.

    Any suggestions for solutions?
    Wednesday, November 11, 2009 2:18 AM

All replies

  • Even though the snapshot agent is shut down, the Log Reader agent continues delivering transactions to the distribution database. These transactions are delivered to subscribers by the distribution agents running for my Transactional publications.

    The problem is not with the snapshot agent itself, or snapshot replication. The problem is with how the transactions are being accumulated and (NOT) cleaned up through an interaction between Snapshot replication, Transactional replication and the code in sp_MSmaximum_cleanup_seqno

    I have gone through the code of and can see what is going on. The sproc responsible for returning the most recent transaction that can be cleaned up, can return the transaction number (binary) delivered by the Snapshot publication distribution agent. And, in my situation it does just that. Given that my Snapshot publication configuration (that creates and distributes the snapshot) runs much less frequently (once a week), the transactions that have occured since then, are being accumulated in the distribution database. Under a normal scenario these transactions would be cleaned out since they have already been replicated by the Transactional publication distribution agents.
    • Edited by jk_o Tuesday, November 17, 2009 9:24 PM snapshot agent -> Snapshot publication distribution agent.
    Monday, November 16, 2009 10:19 PM
  • Kalis, use sp_browsereplcmds. This will show you the commands which are stored in the distribution database. You will only see the snapshot commands there, you will not see any subsequent transactions which have occured since your snapshot was generated.
    looking for a book on SQL Server replication? looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?
    Tuesday, November 17, 2009 1:48 PM

  • I think my point is being missed here. In order to facilitate TRANSACTIONAL replication the Log reader continually pushes transactions into the distribution database, as it should.
    My Transactional Retention is set to 0-7 days. Given that the minimum retention is 0, transactions should be cleaned out regularly (the cleanup job runs as default - every 10 minutes). This is not happening for reasons I've explained above, i.e. the effects of Snapshot Publication using the same distributor as my Transactional Publication.

    Therefore, when I use sp_browsereplcmds I get millions of commands.

    Tuesday, November 17, 2009 9:21 PM