Know Your Limitations
- The Transaction Log has data files on (hopefully by itself) one or more drives. How large is this?
- How much of the Log data file is already full from uncommitted entries?
- For every transaction, it must be logged. So large batches takes a lot of space.
Replication uses BCP files that use MSReplicated objects that insert, update, and delete rows one at a time. You can see these procedures in the target subscriber database under the names
So if you wish to see what is happening on the session level, you can see this during your testing.
Fun Fact: This is also why Replication cannot have IDENTITY constraints allowed
on the target table (you will see the NOT FOR REPLICATION ) because
the batches are not necessarily in that the table has settled in.
Batches Represent a set of transactions that are committed at the same time.
- Set the CommitBatchThreshold flag to a much lower number. Such as 100 and go lower if need be.
Your "fix" actually made some of this worse. From the BOL:
-CommitBatchSize Is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.
-CommitBatchThreshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The
default is 1000.
replication distribution agent | Microsoft Docs
A Batch is a connected set of transactions, usually between a
A batch of SQL statements is a group of two or more SQL statements or
a single SQL statement that has the same effect as a group of two or
more SQL statements.
Batches of SQL Statements | Microsoft Docs
Advanced Techniques: You can guesstimate the amount of space a typical row soaks up using the DATALENGTH() function, if you want to understand more about your LOG.
There is much more to replication, but I am sure this is enough to get you where you want to be. :)