I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
pull subscribers.
A few of my users are having problems with replication. This is all
done via VPN connections to our site (so they're not on our LAN.)
The merge agent fails with the error "The process could not query row
data at the 'Publisher'."
After running Profiler, it appears that the this is the longest
duration of anything run.
I can't find any real details on this stored procedure, so I'm not
real sure what its doing. I'm also NOT a replication expert. I know
enough to have gotten it running and its been working okay, but these
problems have slowly been creeping up. We can normally get the
synchronization to go through, but its frustrating to my users, and I
may be facing a mutiny! Help! I'll be glad to provide any more details
that anyone requests, and if anyone has any details on what this
stored procedure is doing or how I can find out, that would be most
appreciated!
Try setting querytimeout to a large value or using the slowlink
profile.
On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
|||On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> Try setting querytimeout to a large value or using the slowlink
> profile.
> On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
>
>
>
>
> - Show quoted text -
I've done both of those (Set QueryTimeout = 6000) and it still occurs.
|||Index fragmentation could be cuase, you should be rebuilding your merge
system table indexes on a regular basis:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"dday515@.gmail.com" wrote:
> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
>
|||On Nov 15, 1:48 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> Index fragmentation could be cuase, you should be rebuilding your merge
> system table indexes on a regular basis:
> DBCC DBREINDEX (MSmerge_contents, '', 80)
> DBCC DBREINDEX (MSmerge_genhistory, '', 80)
> DBCC DBREINDEX (MSmerge_tombstone, '', 80)
> DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
> DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
> ChrisB MCDBA
> MSSQLConsulting.com
>
> "dday...@.gmail.com" wrote:
>
>
> - Show quoted text -
I've done a full reindex as well prior, still the same problem!
|||Did you try the slow link profile?
Is it possible also that your network link is going down during your sync?
Can you run a ping -t between the publisher and subscriber to verify that
the link stays up during the sync?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Daniel Day" <dday515@.gmail.com> wrote in message
news:3d27eaf9-9105-47e0-96bc-c6afe7763da0@.v4g2000hsf.googlegroups.com...
> On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> I've done both of those (Set QueryTimeout = 6000) and it still occurs.
Showing posts with label duration. Show all posts
Showing posts with label duration. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
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:
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:
Subscribe to:
Posts (Atom)