When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out).

Is there anyway to do this without locking?

  • "Allow Pull Subscriptions" should be unchecked in Subscription options tab screen of publication properties. Note: Pull Subscription does not support "Concurrent Snapshot Generation" – Karthick Aug 11 '14 at 9:46
  • So if I have pull subscribers there is no way? – Kenny Johansen Aug 11 '14 at 11:27
  • Yes it is not possible – Karthick Aug 11 '14 at 11:51
  • How about initialising from a backup? eg msdn.microsoft.com/en-GB/library/ms151705.aspx – wBob Sep 16 '14 at 0:12
  • 1
    So you've done a full backup, done some schema changes, and haven't done a full back up since? Interesting. – wBob Sep 16 '14 at 13:20

The whole point of snapshot is to provide a point in time copy of the articles involved in replication. It does it when generating the snapshot files from the publisher by locking the tables until the snapshot is finished for that particular article.

You should plan to reinitialize during less activity time of your server.

Is there anyway to do this without locking?

In SQL Server 2005, Concurrent snapshot is by default (applies to only PUSH subscriptions)

sync_method = concurrent

What it does is that it produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot.

Not if the new snapshot should include schema changes. – Kenny Johansen Sep 16 '14

Also, as per your comment, if you are doing schema changes (e.g. adding new columns) and want to replicate those across to subscribers, you can use this method.

| improve this answer | |
  • Concurrent snapshot still locks tables when Allow Pull Subscriptions is on. – Kenny Johansen Sep 16 '14 at 11:38
  • I would suggest downvoters to put a comment so that I can improve my answer. The current answer is correct - from BOL - Concurrent snapshot processing, the default with transactional replication, does not hold the share locks in place during the entire snapshot generation, which allows users to continue working uninterrupted while replication creates initial snapshot files. – Kin Shah Jul 30 '15 at 2:12

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.