May 13, 2009 at 9:15 am
Got a company that is using Replication for its reporting solution (let's no go there for now). The problem I'm having is the distribution database's DELETE job is taking upwards of 2 hours each run during the day to delete the commands out of MSrepl_commands table. The job is blocking new replication transactions and user-base is complaining their reports are behind. I'm thinking that since there are fewer transactions during the evening, then maybe I can get the job to delete the aged commands 12 hours after they've been applied to all the subscribers. That way, the DELETE job works harder at night than during the day.
If the stored procedure is:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
and the job runs every 5 minutes (when it's not over-running its 5 minute window),
then that would mean the commands are deleted out of MSrepl_commands table anywhere from 0 to 5 minutes after they've been committed to all the subscriber databases. So if I change the @min_distrentention parameter to 12, then it'll delete them after they've been committed to all subscribers after 12 hours?
I'm I thinking this through correctly? (Also, feel free to offer any other solutions to my dilemma).
Lezza
May 13, 2009 at 10:13 am
First of all it really shouldn't be taking that long. We do a ton of replication and the cleanup agent does pretty well in keeping up. It does tend to cause blocking so limiting the amount of time that it runs can be helpful.
The first thing that I would do would be to rebuild all the indexes on replication tables in the distribution database. We have found this to be a great help in getting this to execute quickly. I would be curious if that helps out with your process.
As to the inputs, I would read through THIS POST[/url] to see if that answers your question.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 13, 2009 at 10:18 am
Some great suggestions David! BTW - I guess I should have mentioned that we're having I/O issues on this server which is why it's taking the DELETE process so long. However, I think rebuildnig the indexes can help tremendously! I'll do that as well. Still need to know about my logic in changing the parameter. The link to the post you gave me is how I decided changing the minimum parameter might help. I want to know if I'm reading this right.
Thanks...
May 13, 2009 at 12:03 pm
From what I read the @min_distretention value is hours so, your above assumption should be correct and that it would indeed wait for 12 hours to delete a command that has been replicated and should "in theory" do as you suspect and work harder at night deleting the commands replicated during the day.
If you think of it post back with your experience after you try this. I would be curious if you find it and / or the rebuilding of indexes helpful.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 14, 2009 at 6:50 am
Well yesterday that MSrepl_commands table had a scan density of 39%. After a index rebuild and half a night of deletes, it has a scan density of 59% and avg page density if 89%. But the Extent switches are almost double the extents.
So better, but still not so great. Delete job is running roughly 17 rows/sec to 45 rows/sec even after the index maintenance.
Today, I try changing the min delete job parameter to 12 hours to see if that helps any. Still looking for any suggestions on making this process a little less invasive. Anyone know what the Applock parameters actually does (in vernacular English)?
Thanks,
Lezza
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply