Replication Of Max Text Length

Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.

Error Description

The length of LOB data (65754) to be replicated exceeds the configured maximum of 65536. Use the stored procedure sp_configure to increase the configured maximum value for the max text reply size option, which defaults to 65536. A configured value of -1 indicates no limit

We ran into an issue with a customer this week. This error was flooding the error log. After a little digging, I found it had to do with transactional replication (also applies to Change Data Capture) they had set up which included LOB data.

Per MSDN,

The max text reply size option specifies the maximum size (in bytes) of text, text, varchar(max), varchar (max), varbinary (max), XML, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536 bytes.

In the error above you can see it plainly states that the column’s LOB data nvarchar(max), in this case, was 65754 bytes which was over the max default size of 65536. Which ironically is 64k.64*1024 = 65536 (if you didn’t know). Adjusting the max text repl size for this server solved our issue. Below you can see the ways to change this value. For us changing it to the max value of 2147483647 bytes which is 2 GB was the way to go. If you don’t know the max value you can also set it to -1 which means no limit, the limit will be based on datatype limits. Previously, the limit was 2GB.

Script

GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max text repl size', 2147483647;
GO

RECONFIGURE;
GO

Using GUI

1. At the Server Level right click and go to Properties.
2. Click on Advanced.
3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.
   

Once we made this change, our problem was resolved.