July 22, 2007 at 8:46 pm
I have a client that we have set both merge and transactional replication between two sites both running Sql server 2005. We are getting connectivity errors for about a 1 minute duration on an hourly basis.
I have checked performance monitor and at these exact times we are getting a CPU spike to 100% also for a minutes duration. I have then checked SQL server for the top 10 Querys by CPU usage and at the same time for a minutes duration the following is being executed.
delete from dbo.msmerge_history where session_id in(select session_id from dbo.msmerge_sessions where subid = @subid) and timestamp not in (Select top 5000 MH.timestamp from dbo.msmerge_history mh join dbo.msmerge_sessions on mh_session_id = ms,session_id where ms.subid = @subid order bt timestamp desc)
If the replication is turned off the problem is rectified - We currently have the merge replication set to never clean the Metadata so I don't understand why the above routine is attempting to clear the history table ?
If anyone could let me know how we could either improve the replications cleanup routine or even schedule to an overnight process or point me in the right direction of optimizing the replication so we don't get these cpu spikes during the business working day it would be greatly appreciated.
Kind regards
David Healy
July 23, 2007 at 1:05 pm
Never clean metadata is only related to the tables:
MSmerge_contents
MSmerge_tombstone
MSmerge_genhistory
MSmerge_current_partition_mappings
MSmerge_past_partition_mappings
MSmerge_generation_partition_mappings
On the other hand,
MSmerge_history
is cleaned up by the Agent History Clean Up in the Distribution database!
Cheers,
* Noel
July 25, 2007 at 6:59 am
You can change the schedule for any of the replication agents, but I don't know what the side affects are of only doing cleanup during evening hours (e.g. letting the history accumulate may cause repl perf issues).
To change the agent schedule, go into repl monitor, click the server, common jobs tab, right-click and do properties of the agent. Choose schedules in the left pane and you can modify what range of time during the day that the agent runs.
July 25, 2007 at 3:02 pm
An Update - First of all thank you for your posts.
I have been trying various things over the last couple of days. I have turned all the Replication cleanup jobs to run once only at 1:00am in the morning. The delete from dbo.msmerge_history still executes on an hourly basis.
We Stoped only the merge replication job and left our transactional replication running and had no spikes so I know that is this specific job that is performing this query.
I then read that Microsoft have changed the cleanup process to be built in to the merge job which would confirm what I am seeing in the results above so I have now changed the Agent profile that the merge job is running and turned off the -MetaDataRetentionCleanup by setting it to 0 from the default of 1 which my understanding should turn off the automatic Meta data cleanup.
Also we deleted our replication and re-setup from scratch and have changed the retention period to only keep a days worth of Meta data. The system ran for a full day without spikes but they returned the following day is this due to the retention period ?
The strangest thing is these spikes continue through the night when there is no activity in the table?
This is a system that is currently in production and we are having to disable the merge replication during the day and enable the merge replication on an evening when they have finished using the system which is not ideal.
I have other sites that run Merge and transactional replication with no issues the only difference is that they are running on SQL server 2000 not 2005.
I need to know what profile or setting is causing this command to execute so I can disable and re-schedule or if somebody knows what is causing this behavior so we can rectify
any help would be greatly appreciated
Kind Regards
David Healy
July 29, 2007 at 3:21 pm
We have now changed the synchronisation from continous to scheduled every 5 minutes and have now run without this issue for 3 days so looks like the problem has been rectified.
Kind Regards
David Healy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply