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
Showing posts with label stuck. Show all posts
Showing posts with label stuck. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Merge Replication not working after 1st Sync
I am really stuck on this, if anyone has some insight into this problem
any help would appreciated...
I'll try to explain what is happening the best I can:
We have a server running Windows Advanced Server 2000 (SP4) w/ SQL
server 2000 (SP3a) (from now on Server A). I have a publication on this
machine with dynamic filters (Changing the HOST_NAME()). The
publication is sending the snapshots to another machine (desktop
machine). The Mobile agent is in the same machine as the snapshots.
The mobile application is syncing fine when hitting Server A. The sync
is done Asynchronously.
Then we have Server B. Running Windows Server 2003 (SP1) w/ SQL 2000
(SP4), same publication w/ dynamic filter however the snapshots and the
mobile agent are in this server.
The mobile application will sync the 1st time but any subsequent syncs
will not work. I check on the Replication monitor and it tells me that
the Merge was a success but the mobile application will not execute the
download table callback, it will execute the Sync callback 5 times and
not proceed in executing the download table callback.
If I change the configuration on the mobile app to point to Server A
the sync will work just fine but, if I change it back to Server B the
sync will work once then it will stop working.
Anyone have some suggestions for troubleshooting?
Update on the situation, it turns out the Sync works fine, what's
going on is that when I sync to Server A, the average sync time is 3
minutes for 2000 rows, on Server B it's taking 45 minutes to sync 1000
rows, any ideas on how to improve/troubleshoot the situation?
I also ran profiler but I have no clue what to search for. In profiler
I couln't find any issues or unless I am not looking for the right
things. Can someone tell me what I can look for in profiler if there
is anything to look for?
Specs for Server A:
CPU: 2 Pentium 3 (550 MHz each)
RAM: 3 GBs
OS: Server 2000 Advanced (SP4)
SQL 2000 SP3
Specs for Server B:
CPU: 2 Xeon Dual-Core (2.8 GHz each)
RAM: 4 GBs
OS: Server 2003 (SP1)
SQL 2000 SP4
any help would appreciated...
I'll try to explain what is happening the best I can:
We have a server running Windows Advanced Server 2000 (SP4) w/ SQL
server 2000 (SP3a) (from now on Server A). I have a publication on this
machine with dynamic filters (Changing the HOST_NAME()). The
publication is sending the snapshots to another machine (desktop
machine). The Mobile agent is in the same machine as the snapshots.
The mobile application is syncing fine when hitting Server A. The sync
is done Asynchronously.
Then we have Server B. Running Windows Server 2003 (SP1) w/ SQL 2000
(SP4), same publication w/ dynamic filter however the snapshots and the
mobile agent are in this server.
The mobile application will sync the 1st time but any subsequent syncs
will not work. I check on the Replication monitor and it tells me that
the Merge was a success but the mobile application will not execute the
download table callback, it will execute the Sync callback 5 times and
not proceed in executing the download table callback.
If I change the configuration on the mobile app to point to Server A
the sync will work just fine but, if I change it back to Server B the
sync will work once then it will stop working.
Anyone have some suggestions for troubleshooting?
Update on the situation, it turns out the Sync works fine, what's
going on is that when I sync to Server A, the average sync time is 3
minutes for 2000 rows, on Server B it's taking 45 minutes to sync 1000
rows, any ideas on how to improve/troubleshoot the situation?
I also ran profiler but I have no clue what to search for. In profiler
I couln't find any issues or unless I am not looking for the right
things. Can someone tell me what I can look for in profiler if there
is anything to look for?
Specs for Server A:
CPU: 2 Pentium 3 (550 MHz each)
RAM: 3 GBs
OS: Server 2000 Advanced (SP4)
SQL 2000 SP3
Specs for Server B:
CPU: 2 Xeon Dual-Core (2.8 GHz each)
RAM: 4 GBs
OS: Server 2003 (SP1)
SQL 2000 SP4
Monday, March 12, 2012
Merge Replication Issues
Hello All,
I would greatly appreciate some help on this issue. I am very stuck and
struggling.
Basicall I have a SQL 2000 Server, that is the publisher for a merge
replication. This merge replication, is to replicate to handhelds, that via
a mobile application, insert into the tables. The handhelds seem to be
operating fine. The publisher is not however. When I try and insert records
into the tables via web interface, I keep getting:
The identity range managed by replication is full and must be updated by a
replication agent.
So I've read for hours online searching for this issue, tons of people have
hit it, but nobody has a solid answer. Most people just hack crap together
that works, or sometimes works.
Basically how I configured it in the beginning was, each subscriber, and
publisher got 200 identities, using automatic identity range management.
Then the website started to hit problems. So I read some articles, some
people said to set the identity ranges to something high that may never get
hit. So I changed them to 60,000. This doesn't seem to work either. Every
day now, sometimes twice a day, I'm getting range is full errors.
Sometimes I can temporarily fix this by calling stored procedures manually
via:
Sometimes works:
EXEC sp_adjustpublisheridentityrange "PublicationName"
Works more often:
DBCC CHECKIDENT ('TableName', RESEED)
But right now, I have one of our customer databases that neither of these
are working.
I would very much appreciate any help on this. It seems theres many myths
about merge replication not working well at all, and its a huge pain to
manage, if you are even able to make it work. I would greatly appreciate
some help as I'm very stuck. Perhaps contact with a MS rep would be great as
well but not very likely I'm sure.
Perhaps theres some tables I can look up that has values of settings that
could of gotten messed up, that I could show to someone who understands them,
and trouble shoot there if that exists.
Thanks all for listening.
Take care,
Simon
As a side note,
I just looked at the tables constraints, and it says this:
([VID] > 21100 and [VID] < 21200)
Is this correct? Is this an issue that I configured it originally for 200
ranges, then changed it to 60,000 ranges?
Also on one table that all the stored procedures don't work, there are 2
constraints on one table for inserts and both are from replication. This
sounds bad. Something is messed up I think?
Thanks,
Simon
"Si" wrote:
> Hello All,
> I would greatly appreciate some help on this issue. I am very stuck and
> struggling.
> Basicall I have a SQL 2000 Server, that is the publisher for a merge
> replication. This merge replication, is to replicate to handhelds, that via
> a mobile application, insert into the tables. The handhelds seem to be
> operating fine. The publisher is not however. When I try and insert records
> into the tables via web interface, I keep getting:
> The identity range managed by replication is full and must be updated by a
> replication agent.
> So I've read for hours online searching for this issue, tons of people have
> hit it, but nobody has a solid answer. Most people just hack crap together
> that works, or sometimes works.
> Basically how I configured it in the beginning was, each subscriber, and
> publisher got 200 identities, using automatic identity range management.
> Then the website started to hit problems. So I read some articles, some
> people said to set the identity ranges to something high that may never get
> hit. So I changed them to 60,000. This doesn't seem to work either. Every
> day now, sometimes twice a day, I'm getting range is full errors.
> Sometimes I can temporarily fix this by calling stored procedures manually
> via:
> Sometimes works:
> EXEC sp_adjustpublisheridentityrange "PublicationName"
> Works more often:
> DBCC CHECKIDENT ('TableName', RESEED)
> But right now, I have one of our customer databases that neither of these
> are working.
> I would very much appreciate any help on this. It seems theres many myths
> about merge replication not working well at all, and its a huge pain to
> manage, if you are even able to make it work. I would greatly appreciate
> some help as I'm very stuck. Perhaps contact with a MS rep would be great as
> well but not very likely I'm sure.
> Perhaps theres some tables I can look up that has values of settings that
> could of gotten messed up, that I could show to someone who understands them,
> and trouble shoot there if that exists.
> Thanks all for listening.
> Take care,
> Simon
I would greatly appreciate some help on this issue. I am very stuck and
struggling.
Basicall I have a SQL 2000 Server, that is the publisher for a merge
replication. This merge replication, is to replicate to handhelds, that via
a mobile application, insert into the tables. The handhelds seem to be
operating fine. The publisher is not however. When I try and insert records
into the tables via web interface, I keep getting:
The identity range managed by replication is full and must be updated by a
replication agent.
So I've read for hours online searching for this issue, tons of people have
hit it, but nobody has a solid answer. Most people just hack crap together
that works, or sometimes works.
Basically how I configured it in the beginning was, each subscriber, and
publisher got 200 identities, using automatic identity range management.
Then the website started to hit problems. So I read some articles, some
people said to set the identity ranges to something high that may never get
hit. So I changed them to 60,000. This doesn't seem to work either. Every
day now, sometimes twice a day, I'm getting range is full errors.
Sometimes I can temporarily fix this by calling stored procedures manually
via:
Sometimes works:
EXEC sp_adjustpublisheridentityrange "PublicationName"
Works more often:
DBCC CHECKIDENT ('TableName', RESEED)
But right now, I have one of our customer databases that neither of these
are working.
I would very much appreciate any help on this. It seems theres many myths
about merge replication not working well at all, and its a huge pain to
manage, if you are even able to make it work. I would greatly appreciate
some help as I'm very stuck. Perhaps contact with a MS rep would be great as
well but not very likely I'm sure.
Perhaps theres some tables I can look up that has values of settings that
could of gotten messed up, that I could show to someone who understands them,
and trouble shoot there if that exists.
Thanks all for listening.
Take care,
Simon
As a side note,
I just looked at the tables constraints, and it says this:
([VID] > 21100 and [VID] < 21200)
Is this correct? Is this an issue that I configured it originally for 200
ranges, then changed it to 60,000 ranges?
Also on one table that all the stored procedures don't work, there are 2
constraints on one table for inserts and both are from replication. This
sounds bad. Something is messed up I think?
Thanks,
Simon
"Si" wrote:
> Hello All,
> I would greatly appreciate some help on this issue. I am very stuck and
> struggling.
> Basicall I have a SQL 2000 Server, that is the publisher for a merge
> replication. This merge replication, is to replicate to handhelds, that via
> a mobile application, insert into the tables. The handhelds seem to be
> operating fine. The publisher is not however. When I try and insert records
> into the tables via web interface, I keep getting:
> The identity range managed by replication is full and must be updated by a
> replication agent.
> So I've read for hours online searching for this issue, tons of people have
> hit it, but nobody has a solid answer. Most people just hack crap together
> that works, or sometimes works.
> Basically how I configured it in the beginning was, each subscriber, and
> publisher got 200 identities, using automatic identity range management.
> Then the website started to hit problems. So I read some articles, some
> people said to set the identity ranges to something high that may never get
> hit. So I changed them to 60,000. This doesn't seem to work either. Every
> day now, sometimes twice a day, I'm getting range is full errors.
> Sometimes I can temporarily fix this by calling stored procedures manually
> via:
> Sometimes works:
> EXEC sp_adjustpublisheridentityrange "PublicationName"
> Works more often:
> DBCC CHECKIDENT ('TableName', RESEED)
> But right now, I have one of our customer databases that neither of these
> are working.
> I would very much appreciate any help on this. It seems theres many myths
> about merge replication not working well at all, and its a huge pain to
> manage, if you are even able to make it work. I would greatly appreciate
> some help as I'm very stuck. Perhaps contact with a MS rep would be great as
> well but not very likely I'm sure.
> Perhaps theres some tables I can look up that has values of settings that
> could of gotten messed up, that I could show to someone who understands them,
> and trouble shoot there if that exists.
> Thanks all for listening.
> Take care,
> Simon
Labels:
andstruggling,
appreciate,
basicall,
database,
greatly,
merge,
microsoft,
mysql,
oracle,
publisher,
replication,
server,
sql,
stuck
Friday, March 9, 2012
merge replication identity range drives me crazy
Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...Originally posted by Pera Pisar
Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble... |||I've solved the problem.
Some old settings for an early ajusted replication remained, so there were some constraints that didn't pass.
I didn't know that settings like identity range remain after the replication is deleted. Or did I do something wrong?
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...
Quote:
Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...
Some old settings for an early ajusted replication remained, so there were some constraints that didn't pass.
I didn't know that settings like identity range remain after the replication is deleted. Or did I do something wrong?
Subscribe to:
Posts (Atom)