Transactional Replication Never Clearing Distribution Database

[Terry Steyaert]

We have multiple instances of transactional replication. (All using the same
publisher/distributor, with various subscribers.) There are three versions,
one goes to two subscribers, the other two each go to one subscriber. There
are also three snapshot replications configured, although they haven't been
used in months. (For initial syncing of the subscriber database.)

According to Replication Monitor, all my transactional replications are up
to date, although they do show transactions being processed occasionally. My
problem is the distribution database has grown tremendously, and never seems
to clear. The "Distribution clean up: distribution" job is being run every
ten minutes, but always seems to clean nothing. No errors are reported. A
message such as "Removed 0 replicated transactions consisting of 0 statements
in 37 seconds (0 rows/sec)." is generated but never seems to be a value other
than zero. The job runs every ten minutes (default) and while it says it
completed in 37 seconds, it sometimes takes over 10 minutes to complete.

Considering replication monitor shows all four subscriptions running, I'm
not sure what else to look at. I'm guessing our distribution database isn't
"quite" correct and thinks at least one subscriber is behind. Any clue how
to find what subscriber isn't up to date?

Thanks in advance,

Terry

[Terry Steyaert]

I have checked further and found one problem, but I'm still not getting any
clearing (that I can tell) of my distribution database. My distribution
database is now bigger than my source and all four target databases combined.

In checking dbo.MSdistribution_status, I found several agents and articles
that had "UndelivCmdsInDistDB". These were caused by several virtual agents.
Running:

execute sp_changepublication @property = 'allow_anonymous', @value =
'false', @publication = 'PublicationName'
execute sp_changepublication @property = 'immediate_sync', @value = 'false',
@publication = 'PublicationName'

Removed those virtual connections, so now I rarely see information under
"UndelivCmdsInDistDB" but I still have HUGE numbers in DelivCmdsInDistDB. I
do have the "Distribution clean up:distribution" running but it keeps
returning with "Removed 0 replicated transactions consisting of 0 statements
in 198 seconds (0 rows/sec)." The time keeps increasing, but the 0 appears
to always stay the same.

Any help or ideas would be greatly appreciated.

Terry

[Paul Ibison]

Try to run the following:

EXEC sp_changepublication
@publication = 'your_publication_name',

@property = 'allow_anonymous',
@value = 'false'

GO

EXEC sp_changepublication
@publication = 'your_publication_name',

@property = 'immediate_sync',
@value = 'false'

GO

Subsequently running

Select * from distribution.dbo.MSsubscriptions will show no virtual
subscriptions.

After that running the “Distribution clean up: distribution” job should
result in a huge difference to the size of the data.

(see http://www.replicationanswers.com/TransactionalOptimisation.asp).

HTH,

Paul Ibison