distribution clean up not working

Using transactional replication and all my transactions and commands are
replicated to all my subscribers. There is nothing to be delivered from the
msdistributionstatus view , but yet when i run the distribution cleanup, the
commands and transactions are still in the msrepl_commands and trans table.
When would they get deleted ?
Do you have anonymous subscribers enabled? If so, the commands will hand
around until the transaction retention perios is reached.
Rgds,
Paul Ibison, SQL MVP
|||How do I find out ? And if they are enabled, how do i turn them off ? I know
making certain settings to a publication causes the whole publication to
initilaize and resnapshot.
It happened once to me when i changed it to concurrent snapshot while
replication was on and next thing i know it triggered a reinitialisation and
all my objects were being snapshot.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:esOIyMDbFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Do you have anonymous subscribers enabled? If so, the commands will hand
> around until the transaction retention perios is reached.
> Rgds,
> Paul Ibison, SQL MVP
>
|||sp_helppublication and look for allow_anonymous.
sp_changepublication can be used to alter.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this reinitialise all subscriptions to the publication ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also allow_anonymous = 0 .. So why is not cleaning up ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's possible the cleanup agent is being blocked.
I have experienced a similar problem but only on databases with really huge
tables (50 million or so), and still have an open PSS on it. The
recommendation was to reduce the transaction retention period, which was not
ideal. Even when I stopped the logreader and distribution agents, the
cleanup still didn't remove the records, for some strange reason, until the
retention period was reached. So, I made sure my subscribers had
synchronized then really reduced the retention period to remove the backlog.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Where do you change the transaction retention period ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uNdEoJEbFHA.2124@.TK2MSFTNGP14.phx.gbl...
> It's possible the cleanup agent is being blocked.
> I have experienced a similar problem but only on databases with really
huge
> tables (50 million or so), and still have an open PSS on it. The
> recommendation was to reduce the transaction retention period, which was
not
> ideal. Even when I stopped the logreader and distribution agents, the
> cleanup still didn't remove the records, for some strange reason, until
the
> retention period was reached. So, I made sure my subscribers had
> synchronized then really reduced the retention period to remove the
backlog.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||It's a distributor property, available from the replication monitor,
distributor properties, properties button of the distribution database.
Rgds,
Paul Ibison