none
Max text replication Size - Interpretation RRS feed

  • Question

  • Fellow SQLers.

    We just started setting up replication.

    Realized that Max text replication Size was set to default of 65536.

    We have since changed it to -1 because we do have lob fileds (ntext, max, etc). And app team got the lob error.

    Accorging to the doco. It leads me to believe that this has nothing to do with replication. What it does is prevent someone updating a column that is flagged for replication and throws error for the lob column if they try to load more than 65536. Is my understanding correct?

    thanks

    MG


    • Edited by mg101 Thursday, December 5, 2019 1:58 PM wording
    Thursday, December 5, 2019 12:42 PM

Answers

  • That is correct, if you do an insert with a lob column value > max text repl size you will get the following message while doing your insert:

    Msg 7139, Level 16, State 1, Line 10
    Length of LOB data (8000) to be replicated exceeds configured maximum 20. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
    The statement has been terminated.

    Your insert will be rolled back.

    If you update a lob column to a value greater than the max text repl size you will get the same message.

    I set this to 20 just for illustrative purposes.

    • Marked as answer by mg101 Thursday, December 5, 2019 3:56 PM
    Thursday, December 5, 2019 3:20 PM
    Moderator

All replies

  • That is correct, if you do an insert with a lob column value > max text repl size you will get the following message while doing your insert:

    Msg 7139, Level 16, State 1, Line 10
    Length of LOB data (8000) to be replicated exceeds configured maximum 20. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
    The statement has been terminated.

    Your insert will be rolled back.

    If you update a lob column to a value greater than the max text repl size you will get the same message.

    I set this to 20 just for illustrative purposes.

    • Marked as answer by mg101 Thursday, December 5, 2019 3:56 PM
    Thursday, December 5, 2019 3:20 PM
    Moderator
  • thx Hilary, That is what I thought

    MG

    Thursday, December 5, 2019 3:56 PM