December 21, 2010 at 3:58 am
I recently setup merge replication and after several weeks noticed the distribution database was growing. I noticed that under SQL agent none of the default tasks were created; Replication appears to be synchronising when querying replication monitor and checking data within random tables.
TASKS:
Agent History Clean Up: Distribution
Distribution Clean Up: Distribution
I believe the above 2 tasks are required to maintain replication, but are missing. Is there a reason why these wouldn’t have been created and more importantly what’s the easiest was to create and add them. I have created merge replication a number of times and these tasks are always created automatically.
March 9, 2011 at 5:12 am
I managed to set the tasks up, but the distribution db continued to grow. looking at what table was the cause of this size it turned out msmerge_history contains 10m records. It doesnt look like data from this table is being deleted;
The following is what I done to resolve;
I have managed to clean out msmerge_history. This table within the distribution database was 7GB containing 10m records and growing. Running the SQL maint task;
agent history clean up: distribution which executes the following on a scheduled basis;
EXEC dbo.sp_MShistory_cleanup @history_retention = 48
didnt seem to be doing anything. I then ran this manually (changing the 48 to 1)
EXEC dbo.sp_MShistory_cleanup @history_retention = 1
and msmerge_history was reduced to around 1000 entries. This has resolved my problem. I just dont understand why changing from 48 hours to 1 cleared this table. I assume the 48 is hours, unless this can be changed to days as this is the only explanation for this. either way it deleted the old data from this table so I fixed the problem but don’t know why.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply