December 3, 2010 at 3:52 am
Distribution.MDF has grown to 2GB within 2 weeks and I believe it will continue to grow resulting in various problems. I noticed that ‘Agent History Clean Up: distribution’ task was missing. I scripted this job from another server using replication and changed @server=N'MY SERVER NAME', I couldn’t see any other parameters that needed to be changed. I ran the script on my server and it succeeded. I ran the task which completed successfully within a minute. I checked the size of distribution.MDF by running a report and the data usages wasn’t reduced. I ran the task multiple times to no avail.
Looking within the Distribution database MSMerge_History contains 2883869 rows, hence believe this is the problem. I thought Agent History clean Up: distribution’ was supposed to maintain the distribution db. Further research suggests data cannot be removed from the distribution DB if changes haven’t been received and delivered to/from the subscriber.
Checking replication monitor the initialization was sitting at about 86% and every so often an error flashed up saying ‘ the merge process is retrying a failed operation made to article ‘table name’ reason the merge agent was unable to synchronise the row due to one or more unanticipated errors in the batch change….’
I have stopped and started replication (using the current snapshot) and its reached 90% with an error ‘thread ID 21244 will wait for 15 seconds before retrying the query on the subscriber.
It doesn’t progress past this point.
I can’t help but thinking replication isn’t completing its sync 100% hence failures are being logged within distribution database resulting in the MSMerge_History growing
I’m using merge replication. Removing and rebuilding replication is a last resort.
January 27, 2011 at 12:19 am
There should be a job named Distribution Cleanup: Distribution which is responsible for clearing the commands from the distibution database on your server.
Distribution Cleanup: Distribution itself has a stored procedure running internally which has two parameters for minimum hrs and maximum hrs.
In ur case, i believe that the job is not able to delete the records. Do one thing execute this stored procedure seperately in SSMS against the distribution database specifying the min hrs and max hrs appropriately. It normally takes some more time for the stored procedure to execute. Once this task is done then u shrink the distribution database.
I believe after performing this task u should be able to reduce the size of the distribution database significantly.
Satnam
January 27, 2011 at 2:08 am
Thanks for the reply, this makes sense. I have completed additional investigation and the problems I encountered all stem from an incomplete merge replication setup. I say this as in my experience when replication is set up maintenance jobs are automatically created. In this example I found I was missing all the jobs, at first I thought it was only ‘Agent History Clean Up: distribution’ (I have never had this issue before where replications management tasks weren’t created – I now include this in my checks when building replication) the following are the jobs I expect to be created,
http://msdn.microsoft.com/en-us/library/ms152762.aspx
Agent History Clean Up: Distribution
Distribution Clean Up: Distribution
Expired Subscription Clean Up
Reinitialize Subscriptions Having Data Validation Failures
Replication Agents Checkup
Replication monitoring refresher for distribution
So I will set up Distribution Clean Up: Distribution and run. Do you or anyone know how to automatically generate these jobs, is it a matter of creating and setting the job up manually?
Ash
February 11, 2011 at 2:17 am
I believe that the distribution jobs gets created automatically when you configure the Replication.
Cheers,
Satnam
February 11, 2011 at 2:40 am
You are correct in that the jobs should be created automatically however they wernt in this example.
In my setup Server A is the Publisher / Distributor and Server B is the subscriber. After breaking replication and rebuilding, several times, still the jobs weren’t created. I then made B the distributor / publisher and server A the subscriber and all the jobs were created on server B however I didnt want to leave the system like this as server A on our sites is always the Distributor and Publisher. I scripted the jobs that were created on server B then removed replication and rebuilt merge replication back on server A as the publisher / Distributor and then applied the scripted jobs (changing the server names and log on were the only changes required, or at least what i could see)
So now Server A has all the jobs that should have been automatically created. I’m currently monitoring the site to see if this has resolved the problem with the distribution DB growing. however, my I originally thought that the cause of the distribution DB growing was that ‘Distribution Clean Up: Distribution’ wasnt running. however I have since found that this Job my default is disabled. I have created replication on several platforms and this jobs is always disabled does anyone know WHY?
March 9, 2011 at 5:13 am
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply