We are running transactional replication in a high volume environment and we are starting to see high IO on the distribution database when the distribution cleanup runs. We only build the snapshot once a week and I was wondering if I rebuilt the snapshot more often it would reduce the size of the distribution database and the amount of IO that are being generated during the cleanup?
Thanks in advance
When we setup transactional replication we are given an option to decide the 'retention period' for replicated transaction. This decides for how long in distribution database any of the transaction from the publisher stays for propagation purposes to the subscribers. Retention period is by default 24 hours. I have noticed if you change it to something larger then you would see distribution database growth and as a side effect whenever the cleanup job runs, it would have large numbers of transactions accumulated during retention period to delete.
Now as you say you have transactional replication, my understanding is that you should have no need to take a new snapshot on weekly basis and re-initialize your subscriber. Snapshot should be taken only when you have a subscriber to initialize otherwise fresh data is automatically propagated by distribution agent to subscribers.
Hope it helps.
|||As Asaf mentioned above, you can reduce the retention rate so your cleanup job cleans up fewer commands, possibliy reducing IO, the other option is to look at your hardware to see if there are bottlenecks you can alleviate. Maybe data file or log file is growing, data/log file are on same disk, maybe you need faster disk, etc.sql
No comments:
Post a Comment