Resolving the "Length of LOB Data (78862) to be Replicated Exceeds Configured Maximum 65536" Error

Understanding the Error

The error indicates that the LOB data size (78862 bytes) exceeds the configured maximum limit (65536 bytes) set for replication in SQL Server. This typically happens during the replication process, leading to the failure of data transfer.

Common Causes

1. Default Configuration Limits: SQL Server has default settings for the maximum size of LOB data that can be replicated.
2. Large Data Inserts: Inserting large multimedia files or extensive text data can exceed the default LOB size limit.
3. Inadequate Configuration Settings: The database settings might not be optimized for handling large LOB data, resulting in replication issues.

Solutions to Resolve the Error

Adjusting the 'max text repl size' Configuration Option

• SQL Server provides a simple yet effective way to handle large LOB data during replication by adjusting the max text repl size configuration option. Here's how you can do it:

EXEC sp_configure 'max text repl size', <desired_value>;
RECONFIGURE;

Replace with the desired maximum size. You can also set it to -1 for the maximum supported size (2 GB).

• After making the configuration changes, restart the SQL Server service to apply the modifications.

Adjusting max text repl size Through SQL Server Management Studio
Additionally, you can adjust another setting in SQL Server to accommodate larger LOB data during replication. This setting is called max text repl size. Here's how you can change it through SQL Server Management Studio (SSMS):

• Open SQL Server Management Studio (SSMS):
  Launch SSMS and connect to your SQL Server instance.

• Right-click the server and select Properties:
  In the Object Explorer, right-click on the server name and
  select Properties from the context menu.

• Go to the Advanced page:
  In the Server Properties window, select the Advanced tab.

• Change the max text replication size:
  In the Miscellaneous section, find the Max Text Replication
  Size option and change it to the desired value. You can set
  it to -1 for the maximum supported size (2 GB).

• Apply and restart:
  Click OK to apply the changes and then restart the SQL Server
  service for the changes to take effect.

Why Adjusting max text repl size Works

By adjusting both the max text repl size and max text repl size configuration options, you're ensuring that SQL Server can handle larger LOB data sizes during replication. This prevents the error from occurring and enables seamless replication processes for your database.

Conclusion

Don't let the "Length of LOB data to be replicated exceeds configured maximum" error halt your database replication efforts. With simple adjustments to the max text repl size configuration option in SQL Server, both through SQL scripts and SQL Server Management Studio, you can overcome this hurdle and ensure seamless replication processes.