SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 textntextvarchar(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.


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

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.


Monica lives in Virginia and is a Microsoft MVP for Data Platform. She has over 15 years of experience working with a wide variety of database platforms with a focus on SQL Server. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group and a Mid‐Atlantic PASS Regional Mentor. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips.


Leave a comment on the original post [sqlespresso.com, opens in a new window]

Loading comments...