Snapshot Publication is not allowing cleanup of the distribution d

[jk]

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?

[Hilary Cotter]

" Therefore, my distribution database accumulates transactions between
executions of the snapshot+sync operations."

This is not quite true. The distribution database will hold all the snapshot
command generated by the snapshot agent, but after the snapshot agent has
done its job it will shut down and changes occuring in the publication
database will stay there.

"jk" <j...@discussions.microsoft.com> wrote in message
news:B3F13C95-0C01-4275...@microsoft.com...

[jk]

First, thanks for your response. But I think you didnt fully understand my
post. 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 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
agent. And, in my situation it does just that. Given that my snapshot agent
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.

jk

"Hilary Cotter" <hilary...@gmail.com> wrote in message
news:%23Oi6avS...@TK2MSFTNGP05.phx.gbl...

[jk]

Just to clarify. In the last paragraph, when I say "snapshot agent", I mean
the distribution agent that delivers the snapshot created via a Snapshot
publication, not the agent that creates the snapshot. Pardon the confusion.

"jk" <j.kre...@orbisfunds.ca> wrote in message
news:OIqjExwZ...@TK2MSFTNGP04.phx.gbl...

[Paul Ibison]

Presumably the problem is reduced if the snapshot agent runs much closer to
the time that the snapshot is delivered?
Is it viable to keep this gap at a minimum?
Cheers,
Paul Ibison

[jk]

The issue is being caused by the gap between the executions of the
distribution agent (which runs the snapshot), not the gap between the
execution of the snapshot and the distribution agent. The earlier gap is a
week, that is I resync once a week, whereas the latter is a few hours, that
is the distribution agent executes the snapshot hours after the snapshot's
creation.