January 29, 2020 at 2:26 pm
Hello,
We've had a snapshot replication process in place for several years. It seems like every couple of years, there would seem to be some sort of hiccup where our transaction log would start to bloat like crazy. When I queried the sys.databases table to look at the log_reuse_wait_desc, "REPLICATION" would be the value. So we would delete replication and recreate it, and that would fix the issue.
This issue has now started happening more frequently over the past couple of weeks. I should note that the replication job agents are only scheduled to actually replicate the data to another database once a week on Sunday mornings, but the hiccups seem to occur in the middle of the week (Wednesday, Thursday, etc...). I'm hoping someone can provide me some insight as to what sort of processes can cause replication to run into this issue. What sorts of things am I looking for, and how can this be prevented?
Also, does changing a table definition break replication?
January 29, 2020 at 7:05 pm
My first guess would be that you have some process with the publication that generates a lot of changes during the week, what you refer to as the hiccups. Having the replication agent run more often than once a week would address that. It's not unusual to have the agent run continuously but it depends on the business needs and requirements.
Many schema changes, including alter table are supported with replication and won't break things. Just don't do the changes using SSMS as that won't work. There is more information on schema changes in the documentation:
Make Schema Changes on Publication Databases
Sue
January 29, 2020 at 11:18 pm
I tend to agree with Sue_H on this, there are processes doing a lot of updates every now and then (batch loads, etc.) and yes, the log records will be being held until Replication is finished with them. I'd be setting the Subscriber to synchronize more frequently.
f you only need the data once a week, why use Transactional Replication? I'd move to Log shipping with a weekly apply of the logs, or a bespoke system doing Differentials or similar.
I'd not use Snapshot Replication because even though Snapshot has no need for the transaction log records, it still marks the log as waiting for Replication, and you will still have this problem.
You could do a bit of reading on sp_repldone but use with extreme caution as this releases records in the log.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 30, 2020 at 3:59 pm
Thanks for the responses Sue and Leo.
At this point, my best guess is that the snapshot was impacted due to one of the developers on our team adding and later modifying triggers on a table we are replicating (our replication is also set to replicate all triggers as well). The timing of the log file bloating and the log_reuse_wait_desc of "REPLICATION" seems to match up exactly with the days that he was working on that trigger.
If I'm off-base in assuming that modifications to table triggers could break snapshot replication in this manner, please let me know your thoughts.
January 30, 2020 at 6:50 pm
Interesting, but I don't think, modifying the triggers would break the Snapshot Replication. This would need to be tested. I doubt he broke anything, it's more likely an update that was applied that wrote to the log.
And sorry, I missed that this is already Snapshot Replication. I would question why you replicate triggers because unless your Subscription database is going to be update-able, the triggers are meaningless, and if this were Transactional Replication it could mess with the data on the Subscriber. I had a client who replicated triggers that tracked who updated rows. On the Subscriber this was being set to the SQL Agent Account when the updates were applied, so the Publisher and Subscriber didn't match.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply