March 24, 2004 at 2:01 pm
I am running merge replication on SQL Server 2000 SP3. I have 2 publications on the same database and 3 of the articles are common for both publications. For one of the publications, I added an article after the publication was created. Now, whenever I run the snapshot agent, all of the sp_upd, sp_sel, sp_ins, and sel_ stored procedures for the articles in this publication are updated, the sp_cft stored procedure for the article not in both publications is updated, and the sp_cft stored procedures for the articles in common are re-created, without deleting the original. So after 3 months of running the snapshot agent twice a week, I have (3 sps * 2 per week * 4 weeks per month * 3 months) 72 stored procedure hanging around. Has anyone encountered this issue, and is there a workaround? Thanks.
ms
March 25, 2004 at 6:36 am
But the stored procedures names should be the same. Do you have different versions of the stored procedures?
March 25, 2004 at 12:14 pm
Yes, there are different versions of the sp_cft stored procedures. The prefix is changing with each additional version. The most resent versions have a prefix of zzzzze_.
March 26, 2004 at 12:53 pm
I think SQL create merge stored procedures with each reinitialization you made to the publication. Basically, every time you create a new snapshot.
Merge replication uses mucho more stored procedures that any type of replication.
They are to replicate, sincronize, check priority, etc.
I think you can't do nothing about it.
March 26, 2004 at 1:36 pm
Thanks for the response.
The thing that bugs me is it is only happening on the publication that I added an article to after the initial creation, and has articles in common with another publication. Also, it is only creating the new sps for the common articles. I have a few other publications that created 5 sps for each article, and none of them have been updated since creation. Strange!
March 26, 2004 at 1:50 pm
Strange.
Run sp_helpmergearticle and check if there is any configuration difference between those problematic articles and the rest.
March 29, 2004 at 10:00 am
I think I sorted it out. Looking at sp_helpmergearticle, I noticed that the article I added to the publication had a status of unsynced. So, in my testing environment where I replicated the issue, I reinitialized the subscriptions. It changed the status to active and seemed to solve the issue. Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply