I have a database that has about 200 filtered publications. When we run snapshots it drags down the merge processes and it takes a long time to recover because of the locks.

  1. Is there a way to create a snapshot on a busy database without slowing down the running subscriptions.

  2. what would be the impact of snapshot isolation. ( I realize its not talking about the actual snapshot)

  • Brent Ozar had a blog post on a topic similar to this awhile ago. I can't find it, but what I can recall is that he suggested breaking up your publications. I can't recall was how he suggested to break them up. By breaking them up it helps your snapshot process from tripping on itself, or at least that's what I recall his blog post talking about... Or maybe it was a summary in an email he sent out. Anyways, good luck!! :) – H.79 Apr 13 '18 at 18:21

Here is one thing you can do to keep your database from getting pegged while a snapshot is in place:

  • Set up master -> slave replication.
  • Use the slave replica for snapshots.

If you were to provide the flavor of SQL you are using as well as what mechanism you are using to create snapshots (i.e Xen, AWS, etc.), I might be able to provide a bit more context.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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