2

I am replicating large tables using transaction replication.

The problem I am facing while applying snapshot at subscriber. Because of large table, active distribution transaction fill-up transaction log which cause to fail Distribution agent.

Action I have taken:

  1. -CommitBatchSize -- 10000
  2. -SubscriptionStreams -- 10000
  3. -BCPBatchSize -- 10000

Yet, I am facing same issue.

Thanks!

  • Transaction log of which database? Publisher, subscriber or distribution? – SqlWorldWide Aug 11 '17 at 15:19
  • Transaction Log of Subscriber database – Rajesh Ranjan Aug 11 '17 at 15:21
  • What is the recovery model of your subscriber database and are you taking transaction log backup frequently? – SqlWorldWide Aug 11 '17 at 15:29
  • Recovery Model is Bulk_Loghed – Rajesh Ranjan Aug 11 '17 at 15:30
  • Did you switch with the intent that it will not be logged? When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model. technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx – SqlWorldWide Aug 11 '17 at 15:35
1

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

[dbo].[sp_MSdel_<article>]
[dbo].[sp_MSins_<article>]
[dbo].[sp_MSupd_<article>]

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 GO statement.

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. :)

  • Last but most important. An unsupported Replication version difference can be really annoying. So check the versions as they will be using different versions of BCP which can affect some articles – clifton_h Aug 12 '17 at 2:35

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.