October 6, 2016 at 9:41 pm
something is not right with replication clean up job. MS_repl_commands keep growing huge (SQL version 2014 SP1)
Made following attempts to resolve below, but its not fixed any help is really helpful.
1)Made replication immediate_sync = o for all publication
2) Execute cleanup via SSMS to delete JUST oldest day. (24 hours @ 5 days = 120), then continue to reduce the @max_distretention valued by a few hours for each run. but it didn't help.
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 120
what are my next step to troubleshoot?
October 7, 2016 at 1:30 pm
bump
any help ?
October 11, 2016 at 2:18 pm
Good start with setting immediate sync OFF.
I'd take a look at your Publication Properties >> Subscription options. Turning off "allow anonymous subscriptions" will let your distribution database delete transactions after they have been delivered - because it "knows" all of your subscribers and won't hold onto stuff in case it gets a new one it has to sync up from the snapshot.
Next, look at your Distribution Cleanup Job under the Agent. Edit the step and look at the command it is running. Mine is set to min_distretention = 0 , max_distretention = 24. Now, my situation is such that I can get away with having zero retention once it is delivered, and only hold up to 24 hours undelivered - our subscriber is just a reporting server, so nothing on it is mission critical - and the DB is small enough to re-sync from scratch in 2-3 hours. Your needs may vary.
Im still new to replication - only been de-facto in charge of replication at work for about 8 months. I'm not a DBA, but nobody else responded - hopefully it was helpful!
Edit: Doh. Just saw the last line of your post. You're ahead of me.
October 11, 2016 at 2:48 pm
Was there any long running maintenance job? If yes , has it completed, index maintenance ..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply