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.

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.

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. 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. 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. You can find Monica blogging at sqlespresso.com 
Bookmark the permalink.

2 Comments

  1. Going over 2147483647 bytes is a fun problem.

    We were replicating medical billing data to a report server and it started falling farther and farther behind. Decided to reinitialize replication and it kept crashing on the same row without much of an explanation.

    Took a look at the row and found one of the vendor’s developers decided to make his (verbose) debug text for medical bill processing (it was a new application) visible to users by storing it in 8 columns in two tables. Eventually a large, complicated bill came through and dumped over 2.5 GB of text in a single column. Over 4GB for the one row across both tables.

    At the same time the network engineers came to us and said that the application had exponential storage growth, and was going to consume 5 years of planned growth in less than 18 months….

  2. Pingback: Replicating Extra-Long Strings – Curated SQL

Comments are closed