Distribution DB increasing in size
-
We have a transaction replication running. We then decided that on a certain application, they would enable a module that would increase data in one of the tables. Prior to this, the Distribution was only 10GB, after 3 days, it is now 27 GB and constantly increasing. I see on the autogrow event that it keeps adding space every so often. Then doing some research, I found out that my MSRepl_Commands has a 101,743,645 record count and adding up.
Both SQL JOBs Agent history clean up: distribution and Distribution clean up: distribution are running every 10 mins.
Doing some research, I decided to change the default values below as follows:
- Transaction retention 72 to 48
- History retention 48 to 24
That brought down MSRepl_Commands record count to 71,317,990 and freed up unallocated space when it was all purple prior to.
Should I change the Delete Batch Size as well? I read that increasing this for high volume transaction replication is preferred. Is there a guide on how much?
TIA
-
You can try increasing it as it often helps in a situation like yours. You have to play around with what to set it to but increasing it to 50000 is usually safe, it depends on your system. You need to play with it and monitor - I don't think there is any one magic number. This post from an Microsoft replication guy has some suggestions:
Customize Distribution Cleanup Batch Size
Sue
-
So I tried different values for Transaction and Commands. It looks like it is still increasing. Will play around some more.
-
Well it looks like it is still increasing even when I changed that from 500,000/20,000.
Will look at decreasing in half Transaction retention 48 to 24 and History retention 24 to 12 but I need to understand/research what the impact of this would be. So far, this replication has been running with no lag or error that I can see on the Replication Monitor.
-
After several tuning, it looks like retention numbers lowers down the count but as you can see, it still creeps up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply