0

Not a unique problem here. I'm more so asking for advice on how to prevent this issue.

We're using a nightly snapshot replication job to offload data to our data warehouse. This job has been setup and running fine for years and recently is causing log growth issues on the publishing database. It fills up our storage drive even though we take transaction log backups every 15 minutes.

When this happens, I run through the normal steps of making sure that log_reuse_wait_desc is showing REPLICATION, I delete the replication, shrink the log, then re-do replication and all is good.

This has happened twice in the last 3 months and I'm wondering what steps I can take to prevent it from happening. I'm not seeing anything out of the ordinary that is causing these issues. Everything is passing the eye test. Not sure if there is something I can create a maintenance plan for and run on a schedule.

Improve this question
2
  • 1
    When it happens, did you verify that the snapshot finished synchronizing to the Subscriber(s)? Does the Replication Monitor show anything of interest? (I haven't used Snapshot replication in a while, not sure if you can check the Undistributed Commands count in there for it, while it's running.)
    – J.D.
    Commented Mar 28, 2023 at 12:08
  • That's the kicker....the snapshots are synchronizing just fine. Now that I check the subscriber settings, it was set to be an always on pull instead of a scheduled pull. I'm betting you're on to something here....the replication monitor is saying everything is OK but the job is showing that it hasn't completed
    – jdids
    Commented Mar 28, 2023 at 14:25

1 Answer 1

Reset to default
0

Did you check if insync backup for the distribution DB is true? If yes, you may need to backup the distribution DB for transaction logs to be released.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/enable-coordinated-backups-for-transactional-replication?view=sql-server-ver16

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.