October 31, 2013 at 8:16 am
How often we need perform maintenance tasks on distribution database? As the data is continuously populated and deleted from the distribution tables, the fragmentation on most of the indexes will be hitting above 90 percent very frequently. Any recommendations ?
October 31, 2013 at 9:06 am
It depends a little on how much data is building up and if its causing an issue. In a high throughput distribution db you can have the replication cleanup jobs running every few minutes to keep the data levels low (assuming the distribution agent is doing its job).
With low data levels index fragmentation isn't a problem.
October 31, 2013 at 9:46 am
MysteryJimbo (10/31/2013)
It depends a little on how much data is building up and if its causing an issue. In a high throughput distribution db you can have the replication cleanup jobs running every few minutes to keep the data levels low (assuming the distribution agent is doing its job).With low data levels index fragmentation isn't a problem.
Very min data is retained in the distribution database. Recently we are continuously experiencing blocking(>30 mins) on distribution server which in turn causing latency. Trying to figure out exactly what's causing this issue?
the most common causes of blocking are with distribution clean up job blocking the update statements on msdistributin_history table and the rest related to msrepl_commands table.
October 31, 2013 at 10:25 am
That is pretty common and is down to some barely acceptable code (in my opinion) in the clean up procs. In many cases some of this code isn't even required. I've seen the same job issues many times over, in many environments.
I resolved the deadlock issues in my environment by creating my own versions of the MS procs. It's generally not a problem with distribution database itself.
October 31, 2013 at 10:50 am
I appreciate if you can provide more information on the custom stored procs.
August 12, 2017 at 9:27 pm
Can you please share the details what you updated in proc ?
That will really be helpful.
Thank You,
Vaibhav
August 12, 2017 at 9:27 pm
Can you please share the details what you updated in proc ?
That will really be helpful.
Thank You,
Vaibhav
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply