sql server replication: Recently I have experienced problems with subscriptions getting out of sync.
Some of the published tables are very large and are connected to the
subscriber via t1 connection. To avoid re-initializing the entire publication
I ran sp_dropsubscription followed by sp_droparticle, both specifying the
article that is out of sync. Then I opened the publication properties from
the replication monitor, went to the articles tab, and added the article back
to the publication. When I run the snapshot again a new snapshot is generated
for that article and it subsequently is pushed to the subscriber. Sometimes
the artilces will get out of sync again in a day or two and some articles
stay synchronized without any problems.

The script I run to drop the article is:
use db1
go

declare @p varchar(128)
declare @a varchar(128)
declare @db varchar(128)

set @p = 'db1_Publication'
set @a = 'tbl1'
set @db = 'db1'

exec sp_dropsubscription @publication = @p ,@article = @a
,@subscriber = 'rptServer' ,@destination_db = @db

exec sp_droparticle @publication = @p
,@article= @a
,@force_invalidate_snapshot = 0

I believe this is the correct way to drop 1 article from a publication that
contains many articles and adding it back again to reinitialize, but I
haven't been able to find any examples of this anywhere.

The question I pose to the replication gurus is, does this work? I am unsure
at this point due to the inconsistent results I've been getting.

Thanks,

Jason,
this is much the same way I do it as well (see
http://www.replicationanswers.com/AddColumn.asp). I would like to
investigate the non-synchronization of these tables though. Do you see any
commands in the distribution database for these tables (sp_browsereplcmds)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul,
I did not browse the replcmds. I was under the impression that there would
be no commands for the article in the distribution database after running
sp_dropsubscription and sp_droparticle. Then adding it back to the
publication and running the snapshot should be a fresh start for that
article. However, I will do this in the future.

Something else to point out, I found a Communication link failure error in
the history of the distribution agent. It retried successfully though. Could
this cause the subscriber to get out of sync?

Thanks,

Jason,
the commands are committed in transactions, so this shouldn't happen.
Perhaps someone has inadvertantly edited the data on the subscriber? Anyway,
next time it happens, let's have a look at the commands in the pipeline
(make sure the cleanup agent doesn't run too often if this is possible) to
see if the commands are there.
You can get the last command sent to the subscriber using this code:

SELECT transaction_timestamp,*
FROM [dbo].[MSreplication_subscriptions]
Delete the last 8 zeros and use this as the timestamp value.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

I think I have figured out why the articles were getting out of sync. The
linked server login the subscribing databases used was not mapped to
repl_distributor. It was mapped to the actual server name. They are both the
same physical server, but repl_distributor has two attributes that the other
one doesn't. dist and system. I discoverd this after executing sp_helpserver
and sp_helplinkedsrvlogin on the production server.

I changed the mapping of this remote login to be mapped to repl_distributor
instead of the actual machine name. Now when there is a communication link
failure replication is able to continue retrying until the communication link
is reestablished.
Once that happens replication is able to resolve any synchronization issues
on it's own.

Thank you for all of your comments and help.
Jason Rowland

[quoted text, click to view]