We may getthe error "Resolve Length of LOB data to be replicated exceeds Maximum 65536 Error on SQL Server" during inserts/updates on a database, on which CDC is enabled for change tracking. This error will occur, when 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 exceeds default value is 65536 bytes. To resolve it, we can set no limit by setting "max text repl size" to -1.
It needs sysadmin or serveradmin role to run the sp_configure command and does not require server restart.
For more details, refer
here.
- USE Database
- GO
- EXEC sp_configure 'show advanced options', 1 ;
- RECONFIGURE ;
- GO
- EXEC sp_configure 'max text repl size', -1 ;
- GO
- RECONFIGURE;
- GO