SQL Server - 2005 Replication Monitor != MSdistribution_status.undelivCmdsInDis

Asked By ravenbr

20-Feb-07 01:45 PM

22 of my 36 distribution agents currently show a total of 400,000+
undistributed commands in MSdistribution_status, but if I look a the agents
individually in SQL Server 2005 Replication Monitor they all show 0
undistributed commands.  And if I use sp_browsereplcmds to get a specific
insert in question I find that it has, indeed, been applied to both
subscribers.

How can I get MSdistribution_status cleaned-up?

20-Feb-07 02:44 PM

Can you run the distribution clean up job? This should take care of it.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

20-Feb-07 02:49 PM

Yes, the distribution cleanup job runs successfully every 10 minutes and has
no effect.

20-Feb-07 03:56 PM

You can safely delete the contents of msrepl_transactions and
msrepl_commands which are beyond the retention period manually.

It seems like you have an ophaned subscription or inconsistent metadata.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

20-Feb-07 05:45 PM

Before I went to anything drastic like manually deleting the commands, I dug
a little deeper and found that each of my publications has four agents
showing in msdistribution_agents.  They have subscriber_id values of -1, -2,
2, and 3.

Subscribers 2 & 3 show a status of 2 in mssubscriptions, a runstatus of
either 3 or 4 in MSdistribution_history, and are the same subscribers that
show up in Replication Monitor and just like in Replication Monitor they show
zero undistributed transactions in the tables that lie under
MSdistribution_status.

The subscriber_id -1 and -2 agents show a status of 1, a runstatus of 0, a
subscriber_db of "virtual", and a xact_seqno of 0x0 (in
MSdistribution_history) which is causing all transactions to show in
MSdistribution_status as undelivered.  These "virtual" agents were created at
the same time and day as the "real" agents.

What are "virtual" subscribers, how were they created, why are they hanging
onto all my transactions but not distributing them anywhere, and how can I
get rid of them?

21-Feb-07 11:16 AM

IIRC virtual subscribers are anonymous subscribers.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

05-Mar-07 10:58 AM

There are two major findings related to the issue of unwanted retention of
delivered transactions in the distribution database that were not apparently
removed with the sp_MSdistribution_cleanup procedure.

A)  Virtual subscribers (-1, -2) are created (or not created) -- see
distribution..MSsubscriptions table -- with the sp_addarticle procedure call,
depending upon the following property settings as specified in the
sp_addpublication procedure call.

1) virtual subscribers created

@allow_pull='true'
@allow_anonymous='false'
@immediate_sync='true'

2) virtual subscribers NOT created

@allow_pull='true'
@allow_anonymous='false'
@immediate_sync='false'

3) virtual subscribers NOT created

@allow_pull='false'
@allow_anonymous='false'
@immediate_sync='false'

4) virtual subscribers created

@allow_pull='false'
@allow_anonymous='false'
@immediate_sync='true'

5) no, not a valid combination of property settings for sp_addpublication

@allow_pull='false'
@allow_anonymous='true'
@immediate_sync='false'

6) virtual subscribers created

@allow_pull='true'
@allow_anonymous='true'
@immediate_sync='true'

The desired combination for our configuration is 3).  In order to remove the
virtual subscriptions, the named subscriptions then publications have to be
removed.  Our replication configuration will have to be recreated ensuring
the modification of the sp_addpublication commands is as stated in 3).

B)  When using the wizard there is a check box on the Snapshot Agent dialog
that states 'Create a snapshot immediately and keep the snapshot available to
initialize subscriptions'.  If it is checked then publication property
combination 6) is used.  If the box is NOT checked, then publication property
combination 2) is used.  If we use the wizard, we should NOT check this box,
then later, modify the publication manually to NOT allow_pull subscriptions.

C)  After A) was determined, it was noticed that the
sp_MSdistribution_cleanup procedure always seemed to fail to delete the most
recent set of delivered transactions.  Troubleshooting revealed a bug -- I
hope this is not a feature -- in the last statement of the procedure
sp_MSmaximum_cleanup_seqno.

The SP1 version of the procedure's last statement is:

select top 1 @max_cleanup_xact_seqno = xact_seqno
from MSrepl_transactions with (nolock)
where
publisher_database_id = @publisher_database_id and
(xact_seqno < @min_xact_seqno
or @min_xact_seqno IS NULL) and
entry_time <= @min_cutoff_time
order by xact_seqno desc

By changing 'xact_seqno < @min_xact_seqno' to 'xact_seqno <=
@min_xact_seqno' the sp_MSdistribution_cleanup procedure will now delete all
delivered transactions.  Also found in the procedure was logic to ignore
cleanup of commands in which the publication has the property
@immediate_sync=1 (true), which further solidifies the conclusion found in A)
to set the @immediate_sync property to (false).

James Brake