Sample Header Ad - 728x90

Merge replication is continually creating the same table on each sync

1 vote
0 answers
44 views
We create a new table in the Publication database and add a new Article to include that table in replication. We then create a new snapshot. The next sync will duly create that new table in the subscription(s) database, however, subsequent syncs will again create that table. This behavior repeats for each sync. This issue has only popped up in our last two Publication updates and we have successfully done many previous updates that involved new tables. The very strange thing is that if the sync is going to a subscriber database in the same SQL Server instance (i.e. a test subscriber on the same SQL Server as the Publication) then there is no problem. The issue only happens when the subscriber database is on another SQL Server. The sync will complete fine, uploading all schema changes and data as expected, but on the next sync, will drop the new table(s) and re-create them again. We examined the merge agent logs very closely and could see various stored procedures in action i.e.sp_MSissnapshotitemapplied, sp_MSrecordsnapshotdeliveryprogress and table MSsnapshotdeliveryprogress. I enabled Profiler during the syncs but have yet to identify the issue. I then created a simple ReplTest database, having one non-partitioned table which was added to the ReplTest Publication, with a subscriber database on both the Publication server and a remote server. Unfortunately, the issue did not appear in this simplifed setup. Our production Publication has 120+ tables, many of which have Article Filters 9i.e. partitioned) and 200+ stored procs. My next step would be to add a Filtered Article into ReplTest and do all the monitoring again using the agent logs and Profiler, however, I am hoping that someone can shortcut all that work and provide any clues ???
Asked by Greg Youdale (11 rep)
Mar 25, 2025, 05:46 AM
Last activity: Mar 25, 2025, 12:16 PM