Merge replication very very slow

Trying to merge replicate a database, but it is very slow.
At the subscriber it inform: "Uploading data changes to the Publisher"
and then stuck for very long time.
After that it informs several lines of: "Processing article
'TableName'" and fail with "General network error".
Publisher, distributor and subscriber: SQL 2K SP3.
I am using the replmerg.exe to replicate, so I am getting all logs on
screen anyway and it looks like it fail in different phases each time.
I already set QueryTimeout to 600 and inactivity threshold to 120.
Thanks

Merge replication very very slow

You could enable logging as per this article:
http://support.microsoft.com/?id=312292 in order to try
to get more info.
To troubleshoot connectivity issues:
(a) run a ping -t for a day and then look at the
statistics to see if a hicup occured.
(b) You could run this keep alive script which will tell
you exactly when you get a failure. Save this as
c:\keepalive.sql
WHILE 1<>2
BEGIN
SELECT GETDATE()
WAITFOR DELAY '00:00:01'
END
GO
Here is the batch file that Hilary posted up a while back
to run this script:
osql -S hilary2kp -E -i c:\keepalive.sql -n
date /t
time /t
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I'm not convinced that this is a network error. I think it could be that the
merge agent is busy processing the articles and has not completed within the
inactivity threshold and the error has resolved as a general network error.
Logging will help you to determine exactly what the problem is, but you
should bump up the QueryTimeout values and the inactivity threshold levels
in hopes that the added time interval your merge agent has to work with will
allow it to successfully complete.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d1201c4d7b9$9a29f8a0$a501280a@.phx.gbl...
> You could enable logging as per this article:
> http://support.microsoft.com/?id=312292 in order to try
> to get more info.
> To troubleshoot connectivity issues:
> (a) run a ping -t for a day and then look at the
> statistics to see if a hicup occured.
> (b) You could run this keep alive script which will tell
> you exactly when you get a failure. Save this as
> c:\keepalive.sql
>
> WHILE 1<>2
> BEGIN
> SELECT GETDATE()
> WAITFOR DELAY '00:00:01'
> END
> GO
> Here is the batch file that Hilary posted up a while back
> to run this script:
> osql -S hilary2kp -E -i c:\keepalive.sql -n
> date /t
> time /t
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Merge Replication Status

I'm using sp_replmonitorhelpmergesession to determine the status of
multiple push subscriptions.
When I set the Merge Agent to Slow Link, I get a status of 4 and the
message "No Data needed to be Merged" when there are no changes.
I'd like to get it to return the Status 4 when it's idle, but I want
to increase the polling interval from 60 to 180.
So I copy the Agent Profile "Slow Link" and alter the polling interval
and validation intervals only.
When I copy the slow Link Profile and use the copy, I get a Status of
3 and the message "Waiting xxx second(s) before polling for further
changes."
Where/how are these controlled?
pollinginterval
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Bunin" <BBunin@.SBCGlobal.net> wrote in message
news:1176335136.270222.63650@.n59g2000hsh.googlegro ups.com...
> I'm using sp_replmonitorhelpmergesession to determine the status of
> multiple push subscriptions.
>
> When I set the Merge Agent to Slow Link, I get a status of 4 and the
> message "No Data needed to be Merged" when there are no changes.
> I'd like to get it to return the Status 4 when it's idle, but I want
> to increase the polling interval from 60 to 180.
> So I copy the Agent Profile "Slow Link" and alter the polling interval
> and validation intervals only.
> When I copy the slow Link Profile and use the copy, I get a Status of
> 3 and the message "Waiting xxx second(s) before polling for further
> changes."
> Where/how are these controlled?
>
|||On Apr 12, 8:48 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> pollinginterval
> --
> Hilary Cotter
>
Yes, but why does changing that alter the behavior of the Status and
messages?

Merge Replication Slow after period of inactivity

Hi Guys,
I don't know if you can help, I have a couple of issue with Merge
replication running on SQL Server 2000 SP3a.
I install merge replication and build all the snapshot for the pull
subscriptions and create the subscriptions running on the local machines
(MSDE 2000). This particular installation has 70 local machines and 140
subscriptions.
The problem I am seeing is that if the machines are not communicated for a
period of time, even though no data has changed on the local machines or
server, it can take ages for the communications process to complete,
sometimes in excess of 3 hours.
If anyone could shed some light on this issue I would be greatly
appreciated.
Thanks Tim.
Have a look at this white paper for hints on what causes this and how to
improve its performance.
http://msdn.microsoft.com/library/de...artitioned.asp
"Tim Ford" <timford@.removethisin2focus.com> wrote in message
news:#iNZR#ptEHA.2452@.TK2MSFTNGP10.phx.gbl...
> Hi Guys,
> I don't know if you can help, I have a couple of issue with Merge
> replication running on SQL Server 2000 SP3a.
> I install merge replication and build all the snapshot for the pull
> subscriptions and create the subscriptions running on the local machines
> (MSDE 2000). This particular installation has 70 local machines and 140
> subscriptions.
> The problem I am seeing is that if the machines are not communicated for a
> period of time, even though no data has changed on the local machines or
> server, it can take ages for the communications process to complete,
> sometimes in excess of 3 hours.
> If anyone could shed some light on this issue I would be greatly
> appreciated.
> Thanks Tim.
>

Merge Replication on SQL Server 2000 -- too slow?

I'm setting up a Merge replication over a very high bandwidth line, and I'm
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:

> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:

> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.

> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:

Merge Replication is very slow

Hi
We have configured merge replication in SQL 2005 (SP 1) for a database
having more than 100 GB. The merge agent duration was for every 5 mins.
Normally it used to take around 2 to 3 mins to complete. It was working fine
till now but now it is taking around 45 minutes. When checked and viewed
details in the replication monitor the below message was displayed for a long
time.
The process is waiting for a response from the query '{call
sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
Any help in this regard is appreciated
Thanks.
Are you using join Filters? Also you might want to drop your retention
period so that metadata is purged more frequently.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
> Hi
> We have configured merge replication in SQL 2005 (SP 1) for a database
> having more than 100 GB. The merge agent duration was for every 5 mins.
> Normally it used to take around 2 to 3 mins to complete. It was working
> fine
> till now but now it is taking around 45 minutes. When checked and viewed
> details in the replication monitor the below message was displayed for a
> long
> time.
> The process is waiting for a response from the query '{call
> sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
> Any help in this regard is appreciated
> Thanks.
|||In addition to Hilary's suggestions, look at if you really need all the data
at the subscribers. Does all data change at both places, if not utilize the
downloadonly articles. And when using filters, see if you can use any of the
filtering enhancements in SQL Server 2005.
Also was there a lot of activity on the database recently that is causing
this wait?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
> Are you using join Filters? Also you might want to drop your retention
> period so that metadata is purged more frequently.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
>
|||Thanks for the reply
I don't have any join Filters.
The retention period was 14 days and now changed to 7 days. Removed some
records from MSmerge_contents &
MSmerge_genhistory.
Now there is some improvement. But still it is taking more than it used to.
Data changes are there at both (subscriber & publisher) ends. It is a 24/7
high activity database.
Checked memory & bandwidth , CPU, etc., all seems to be normal.
Thanks,
"Mahesh [MSFT]" wrote:

> In addition to Hilary's suggestions, look at if you really need all the data
> at the subscribers. Does all data change at both places, if not utilize the
> downloadonly articles. And when using filters, see if you can use any of the
> filtering enhancements in SQL Server 2005.
> Also was there a lot of activity on the database recently that is causing
> this wait?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
>
>
|||If it is a highly active database, then it will be due to metadata growth.
If you can be sure that the subscriber will be synchronizing often, then you
can further reduce the retention. However remember that if the subscriber
fails to synchronize within the retention, it will expire and will need
reinitialization.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:9456F61D-74FD-45AF-BE89-8BFFEFD571DF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply
> I don't have any join Filters.
> The retention period was 14 days and now changed to 7 days. Removed some
> records from MSmerge_contents &
> MSmerge_genhistory.
> Now there is some improvement. But still it is taking more than it used
> to.
> Data changes are there at both (subscriber & publisher) ends. It is a 24/7
> high activity database.
> Checked memory & bandwidth , CPU, etc., all seems to be normal.
> Thanks,
>
> "Mahesh [MSFT]" wrote: