Transactional Replication - Avoid Snapshot locking tables

Copper Contributor

In a Transactional Replication setup, if is necessary to create a new snapshot initially and every time new tables need to be added to the publication.


During Snapshot two phases seems to lock the entire system


1. Immediately after start of snapshot. This is a short disturbance

2. Just before finishing the snapshot every single table in the publication is locked. When using a large number of tables and having a large number of concurrent users on the database, this lock is very frustrating (it takes 15 minutes on our system), and even worse the snapshot may experience a deadlock resolution during this period, causing the snapshot creation to restart (24+ hours).


So it would be very helpful if an alternative to the full lock/stop could be found, allowing the creation of snapshots to be less disturbing, maybe even allowing it to be a produktion-hour online operation.


0 Replies