December 9, 2014 at 9:17 pm
Hi friends,
I noticed in my one database the table update query is slow in merge replicated tables.
Then I checked MSmerge_genhistory and its having more than 4 million records.
Then I tried to update one row of MSmerge_genhistory table and I noticed that table is also taking more time for update.
So please tell me what is the best way for clear older data.
Thanks
December 12, 2014 at 12:19 am
Dear Manju,
Merge Replication use a lots of metadata and tracking tables to store the information about the replicated tables.
Over a period of time a lot of data can accumulate in these tables and can cause serve performance issues.
Main tables that are used for tracking are :
1.MSmerge_contents
2.MSmerge_tombstone
3.MSmerge_genhistory
These tables are trimmed on the basis of the parameter "Retention Period". by default its value is 14. Check the value you have in your setup if it is 0 which actually means infinite then you are not trimming your metadata tables.
To resolve this issue you need to change the value of retention period to a desired value and allow SQL Server to delete the obsolete records. It is advisable not the make a significant change in the value of retention period in one go as SQL Server will try and delete all the obsolete records which could be in the order of millions in one go hence causing a bottleneck. So change the value little by little until you reach the desired value.
Additional Reference :
# http://www.brentozar.com/blitz/merge-replication-with-infinite-history/
# http://msdn.microsoft.com/en-us/library/ms151778(v=sql.105).aspx
hope it helps.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply