Replication 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: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. 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

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 setup which included LOB data.

Per MSDN:  The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(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 data type limits. Prior to 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 the Advanced.
3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

Once we made this change our problem was resolved.