October 19, 2005 at 5:45 pm
Hello there,
My company is using Microsoft SQL Server 2000 and we use Merge Replication.
Here's the merge replication model:
Site A: (merge replication)
- 4 servers - A1, A2, A3, A4
- A1 is the publisher
- A2, A3, A4 subscribe to A1
Site B: (merge replication)
- 3 servers - B1, B2, B3
- B1 is the publisher for site B.
- B1 also subscribes to A1, in order to get Site A's data.
- B2 and B3 subscribe to B1
We recently found out one subscriber server (A2) was being pumped in way too much data, and has now accummulated about 25million rows in the msmerge_content table in one database and 12million rows in another database.
Transactions are and can be written to any subscriber at any time, so we are not able to tear down and setup the replication again.
What's the best way to merge the data back to all the other subscribers?
Is the following a possible solution?
- Shut down the merge agents on the troubled databases
- BCP out the rows which timeframe were affected from A2
- Truncate msmerge_genhistory, msmerge_contents, msmerge_tombstone on A2
- BCP the rows locally into other servers without the fire trigger so it won't attempt to replicate
- Restart the merge agent
Any help is greatly appreciated.
Thanks in advance,
baes
October 20, 2005 at 6:06 am
You are best to let merge replication work its magic or regenerate and redistribute your snapshot.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
October 20, 2005 at 10:07 am
Hi Hilary,
Thank you for your response.
In fact, we noticed this problem since two weeks ago, the merge agent was not able to sync up and merge the data. We tried defragging indexes of all user/system/merge tables, and then restart the merge agent. It has been two weeks and the merge agent still keeps failing at the metadata cleanup.
Meanwhile, Server A2 is our primary server where majority of the data are being pushed into the database. So there are new entries of data coming in every single day and so the merge tables continue to grow in size.
We have searched on BOL and the net on how to reapply the snapshot through removable media, however we haven't found any concrete instruction how this is done. All merely mentioned this can be done but how exactly is this done?
Thanks in advance,
baes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply