Length of replicated LOB data exceeds configured maximum

We had set-up peer-to-peer replication on three geographically distinct nodes with around 800 articles being published with data type of lot many columns as varchar(max), image and text data. Once the replication was up and running, users reported this error “com.microsoft.sqlserver.jdbc.SQLServerException: Length of LOB data (70199) to be replicated exceeds configured maximum 65536”.

Reason for this issue: Default SQL Server configuration supports “Max text replication size” of 65536 bytes (2 power 16 bytes) for text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data columns. We get this exception when in a single insert or update statement data size for any of the replicated text column violates this limit.

Solution: There are two ways to check and rectify this issue. Once using T-SQL and another using SSMS

Using T-SQL
Check the server configuration for current max text replication size

USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size'; 
GO

-- OUTPUT:
-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536

Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.

-- For SQL Server 2005
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size', 2147483647 ; 
GO
RECONFIGURE; 
GO

-- For SQL Server 2008 (and 2008 R2)
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size', -1 ; 
GO
RECONFIGURE; 
GO

-1 indicates that there is no limit other than imposed by the data type itself. Furthermore, max data replication option applies to transactional replication and CDC (Change Data Capture) and is ignored for snapshot replication and merge replication.

Using SSMS

1. Open SSMS and connect to object explorer
2. Right-click on the server name (in explorer) and choose properties
3. Select “Advanced” options on the properties page.
4. Max text replication Size is available under “Miscellaneous” header.
5. Change the value from 65536 to -1 or 2147483647 (depending on the version on SQL Server) and press OK.

 

Advertisement