Replication error "The merge process could not enumerate changes at the 'Publisher'".

Question

Hello,

I am using SQL 2005 SP3 - enterprise edition as publisher , and I have 15 subscribers ... some sql 2005 SP2 express, some on SQL 2005 SP3 enterprise edition.

i have 30 articles in publications .. and all are merge pull publications...

The problem is  .. when ever i synhronize the data, its updating data in articles that havent been changed at all..

For example if i have article that have 300 000 records, every time it will update 300 000 records, even if there is i just 200 new records inserted, (records are inserted just on subscriber) and that takes a lot of time  ... And often stop with error "The merge process could not enumerate changes at the 'Publisher'"(afte 1 hour maybe), and after  15 repeats synhronization goes well, but thats not a solution.


can enyone help me with this issue?

Answer 1

one more information..

i saw in activity monitor,during the synch, that sys.sp_MSenumchanges_belongtopartition;1 is taking a lot of time and resources(CPU and Physical IO) .

Answer 2

It sounds like something in your filter is changing. How are you filtering this table for subscribers?

If you change a value which is part of your filter expression it may cause it to update each record in order for that record to be sent down to the subscribers.

Make sure that each column involved in your filter expressions is indexed. That will speed up the operation of that procedure.

Martin

Answer 3

Here is the situation (although for 2000) like yours:

http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.replication/2007-01/msg00428.html

---
Paul Svirin
StarWind Software developer ( http://www.starwindsoftware.com )

Answer 4

Thank you for your reply...

this article is table for invoices(FactsDet) and i am filtering that article on column facility... each subscriber is diferent facility. This colum facility has non-clustered, non-unique index..

I realise also that if synch goes well lets say in the morning, and there is about 400 000 changes on subscriber(updates on factsdet), every other synch will go well, i mean it will synch just real changes... and this mean just that day... another day .. again he has to do update of all data...

so the first one synch is problem .. is hanging a lot on... enumrating insert and updates for article Factsdet (generation batch 1). But it is for all articles also the situacion, just factsdet is the biggest.. Factsdet is articles for invoices, Deliverdet as article also has a problem,transdet, entrydet and so on and so on.. its not just situation with factsdet..he updates every article, like every data that i have on subscriber... and in all of this articles filtering is on the Facility column...

earlier i changed generation_leveling_treshold to 0 coz i also had problems with some replication locking.. should i set it back to 1000?

Answer 5

i just found why is error in the end "the merge process could not enumerate changes at the Publisher"
The problem is with ISA server, coz i am using it for all my vpn connection... the problem is in some time, connection brake, and i cannot even connect over remote desktop to ISA, also my vpn users(subscribers) ... and sometimes after 15 minutes everything is ok, and sometimes i need to restart isa.. i know that this is not part of topic but mybe it could help...

so the replications takes long coz of the update problem,  which is excelent for braking the connection by isa :)) .. i read somewhere that isa can lock down coz of too many active logs.. but i am not shure how to fix it..

so interesting situations i have here .. :)

Answer 6

i still cannot find the reason of updates of  all data every morning.. on all subsrcibers...

doeas anyone know what could it be ??

Answer 7

it seems that i find the problem.. it was something like u said Martin... there is some procedure that updates some information to the tables, and filters whas set wrong so it updated a lot of unnessesary data, so the subscribers were updated too, i fixed it.. and i hope it will be fine now..

Thank you both for your help...