6

I have a table replicated via transactional replication that is throwing this error message:

Length of LOB data (68983) to be replicated exceeds configured maximum 65536.

This can be resolved by adjusting the "max text repl size (B)" SQL Configuration value from the default of 65536.

I see two options:

  1. Adjust the value higher than required for this situation
  2. Set the value to "-1" which indicates no limit

My question is: What is the downside of setting "max text repl size (B)" to -1?

I appreciate any feedback!

1 Answer 1

1

What is the downside of setting "max text repl size (B)" to -1?

I would not set the value to -1 (limitless). I would instead find out the max datalength of your lob data and set it accordingly with a 10% buffer. This way you have more control of what and how much gets into distribution.

I can see that in an unlikely situation, setting of -1 can cause high network latency if the blob data to be replicated is of a much larger size.

3
  • 1
    Actually, setting to -1 is not bad when you are not sure of your largest lob datalength. Whether replication latency is impacted is by the real blob data volume and size. Just like whether you set a column to varchar(max) depends on your business requirement. I remember in one replication environment, I set this to -1 because from time to time (like once in a week), I will have a few records whose datalength is beyond 64K, and I do not see any issue at all.
    – jyao
    Mar 27, 2018 at 4:12
  • I consider this another "perl" from Microsoft. So, you have a table with text fields which you replicate on a different server (you may have a high availability cluster). In what universe do you want to replicate just data that has fewer bytes than some random value? If I store docs or images, do I want to replicate only the ones that have less than 65535 bytes? What happens to the others? The default value should have been -1 to begin with. In the end it is just more data to be replicated. It should not matter.
    – boggy
    Oct 14, 2020 at 23:14
  • Why is replicating a bigger blob field any different than replicating hundreds of rows per second and of smaller size ?
    – boggy
    Oct 14, 2020 at 23:31

Your Answer

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

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