Have you ever encountered the error message “Length of LOB data to be replicated exceeds configured maximum”? If not, don’t worry, because today we will discuss this error and how to resolve it.

This error typically occurs in SQL Server when using transactional replication or Change Data Capture, and involves LOB (Large Object) data types such as text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image. The error message indicates that the size of the LOB data exceeds the configured maximum value, which by default is set to 65536 bytes (64KB).

Let’s take a closer look at the error message: “Length of LOB data (65754) to be replicated exceeds configured maximum 65536.” In this example, the LOB data in the column nvarchar(max) is 65754 bytes, which exceeds the default maximum size of 65536 bytes.

To resolve this issue, we need to adjust the max text repl size option. There are two ways to do this: using T-SQL or the SQL Server Management Studio GUI.

Using T-SQL

Open a new query window and execute the following script:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max text repl size', 2147483647;
RECONFIGURE;

This script enables advanced options, sets the max text repl size to the desired value (in this case, 2GB), and then reconfigures the server.

Using GUI

If you prefer a graphical interface, follow these steps:

1. Right-click on the server in SQL Server Management Studio and select “Properties”.
2. Click on the “Advanced” tab.
3. Under “Miscellaneous”, locate the “Max Text Replication Size” option and change it to the desired value.

Once you have made the necessary changes, the error should no longer occur.

It’s important to note that if you don’t know the maximum value for your specific data type, you can set the max text repl size option to -1, which means there is no limit. However, keep in mind that the limit will then be based on the data type’s inherent limits.

In conclusion, the “Length of LOB data to be replicated exceeds configured maximum” error can be resolved by adjusting the max text repl size option in SQL Server. Whether you prefer using T-SQL or the GUI, both methods will help you overcome this issue and ensure smooth replication or Change Data Capture processes.