none
Distribution log growth when dropping a Merge Publication RRS feed

  • Question

  •  

    I had a Distribution database that was less then 500 megs (data) in size and the log was about 100 megs.

    On of two merge publications was dropped which caused the Distribution Log to grow to over 5 Gigs in size.

    Has anyone ever seen this behavior and what can be done about it?

     

    I have less then a dozen devices syncing using CE and the retention history for the distributor is set to 170 hours.

    The publisher/distributor are on the same SQL Server 2005 instance.

    My current idea is that some special history clean up is being done during the publication drop that is causing the log to grow so much. 

    If I change the retention period down to an hour, then run the Agent Cleanup job, everything is removed nicely; the log does not increase much.  Then the publication can be dropped with out much increase in the distribution log.

     

    This works fine for now but when we increase to 300 CE devices I fear we may not be able to drop the publications with out taking gobs of disk space.

     

    Amy

    Saturday, January 5, 2008 6:51 PM

Answers

  • I will bet it's the MSmerge_history table that was pretty big - 170 hours retention is a lot of history saved, especially with 300 devices syncing.  My guess is the cleanup is done in a large transaction, which is why your log grew so big.

    Sunday, January 13, 2008 12:51 AM
    Moderator

All replies

  • I will bet it's the MSmerge_history table that was pretty big - 170 hours retention is a lot of history saved, especially with 300 devices syncing.  My guess is the cleanup is done in a large transaction, which is why your log grew so big.

    Sunday, January 13, 2008 12:51 AM
    Moderator
  • I am not up to 300 devices yet, still testing with less then 12. 

    I have been able to run a few tests:

    Test 1 - MSmerge_history had 631998 rows, history at 48 hours instead of 170.

    the distribution log was at 4 megs, dropped the publications, log grew to 670 Megs.

    Test 2 - MSmerge_history had 312532 rows, history at 48 hours instead of 170.

    the distribution log was at 4 megs, dropped the publications, log grew to 503 Megs.

    Test 3 - MSmerge_history had 6663 rows, history of 1 hour.

    the distribuiton log was at 4 megs, dropped the publications, log grew to 12 Megs.

     

    Seems like no history is the way to go here but it is still very odd that the log grows so much.

     

    Amy

     

    Monday, January 14, 2008 10:35 PM