2

I have two SQL Server databases that I am trying to replicate. I am able to replicate small tables without any issues.

However I have a table with over 16 Billion rows that is quite problematic.

I am currently using transactional replication to replicate the large table. From the documents I have read online, when you select transactional replication, the process uses @sync_method = N'concurrent' parameter which is not suppose to lock the table, but when I initiate the process, the table seems to lock which renders it un usable. At the same time when I initiate the subscription i.e on the destination server, it also locks the table at the destination.

I would want to know what am missing or doing wrong such that the table locks. Remember that the table is also in use as the replication happens. So it should not lock at any given time.

Read committed snapshot isolation is already enabled on the database.And this is the initial snapshot that I am trying to run.

Improve this question
6
  • Please provide more details such as if the locking is occurring during the initial snapshot, and how you're aware that the table is locked at the publisher. (You can use Adam Mechanic's sp_whoisactive to help verify locking issues.) It's totally possible you have no locking on the publisher side but you're just facing overall resource contention.
    – J.D.
    Sep 7, 2021 at 17:33
  • Thank you J.D for your response. I try to insert like 100 records into the replicating table, and it just keeps running for minutes. A process that takes seconds in a normal situation. And when I check sp_whoisactive, I see that the insert has been blocked by the replication process. However you could also be right by saying that we are just facing overall resource contention in accordance to the replication monitor which sometimes gives a status of performance critical.
    – Crispin
    Sep 8, 2021 at 6:25
  • 1
    @Cris - please use the edit link to add details to your question. Thanks!
    – Hannah Vernon ♦
    Sep 8, 2021 at 12:15
  • 1
    Please update your original post with those details. Is this the initial snapshot you're trying to run or it was already snapshotted over to the Subscriber?
    – J.D.
    Sep 8, 2021 at 18:22
  • @J.D This is the initial snapshot.I have already updated it on my question.
    – Crispin
    Sep 9, 2021 at 7:06

2 Answers 2

Reset to default
2

A table with 16 billion rows will need more attention and will take longer to replicate no matter how you do it.

I had a similar issue about locking but it was on my subscription servers not the publisher - many moons ago as you can see here.

I had good experience with READ_COMMITTED_SNAPSHOT in replication as you can see here.

The transactional replication on the publisher will not lock the replicated tables this is how it is done: Modifying Data and the Log Reader Agent

However, when initialising the subscriptions or anytime you need a new full snapshot, then yes there will be locks on the table but not exclusive locks and not for the whole duration of the snapshot:

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.

there is this question that deals with the same problem:

How to generate replication snapshot without locking tables

one of the ways to work around that is to

Initialize SQL Server replication using a database backup

Improve this answer
1
  • 1
    Thank you for your response.I am currently trying Initialize SQL Server replication using a database backup and I am hoping it will workout.
    – Crispin
    Sep 9, 2021 at 7:13
0

If you are talking about the table lock while the snapshot agent is running, I think the only way to avoid it would be to enable « read committed snapshot isolation » on the database, but this must be done only if the applications have been tested with this enabled.

Improve this answer
1
  • Thank you Eric Prévost for your response. However read committed snapshot isolation is already enabled on the database.
    – Crispin
    Sep 8, 2021 at 6:39

Your Answer

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

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