Recently, one of our clients encountered an issue while running a data update in SQL Server. The operation failed immediately with a configuration error, specifically targeting Large Object (LOB) data:
Length of LOB data (169,494) to be replicated exceeds configured maximum 65,536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65,536. A configured value of -1 indicates no limit, other than the limit imposed by the data type.
The Red Herring: Replication?
This was an error I hadn’t come across before. The mention of LOB data and “replicated” immediately pointed toward Transactional Replication. Our initial investigation confirmed that the underlying issue is indeed that SQL Server’s max text repl size setting controls the maximum size of LOB data i.e. varchar(max), nvarchar(max), or text, that can be processed by replication-related features. The default limit is 65,536 bytes (~64 KB).
However, there was no replication setup on this SQL instance.
The Real Culprit: Change Data Capture (CDC)
After some further digging, we discovered that this exact error frequently occurs when Change Data Capture (CDC) is enabled.
For those who don’t know – Change Data Capture (CDC) is a SQL Server feature that records insert, update, and delete changes to tables by capturing them from the transaction log into dedicated change tables for easy tracking and downstream processing.
The critical insight is that CDC relies on the same internal mechanisms as transactional replication to process and store changes from the transaction log. Therefore, it is governed by the same max text repl size configuration setting. We confirmed that CDC was indeed enabled on the database.
Analysis: Sizing the LOB Limit
The failed update was part of a MERGE operation involving a column of data type nvarchar(max) containing very large values.
The largest value we found was approximately 188,530 bytes and because CDC attempts to capture the change in this LOB column, the default 64 KB limit was exceeded, causing the transaction to fail.
The Fix: Setting a New Maximum
To resolve this, we needed to increase the max text repl size setting. While the error message suggests using -1 for an “unlimited” setting, this is often discouraged due to potential performance and stability issues. The best practice is to set a value slightly larger than the maximum LOB size you anticipate encountering.
We also checked other similar tables and saw that the max size was circa 7.6MB, thus we decided to use a rounded value for future-proofing against growing data sizes. We chose 8,400,000 i.e. ~8MB, which is the value that best suited this issue.
We ran the following script on the production instance to implement the change:
-- Step 1: Enable advanced options EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO -- Step 2: Set the max text repl size to 8,400,000 bytes (~8MB) -- This allows for LOB data up to 8MB to be processed by CDC. EXEC sp_configure 'max text repl size', 8400000; RECONFIGURE; GO -- Step 3: Disable advanced options (optional, but good practice) EXEC sp_configure 'show advanced options', 0; RECONFIGURE; GO
This configuration change is dynamic and did not require a service restart. Rerunning the update operation immediately resolved the issue.
Proof of Concept: Reproducing the Error Yourself
To better understand this new error, I replicated it using the AdventureWorks2022 database. This section shows the setup, the initial failure, and the minimal configuration required to make the demo work.
Setup and Error Generation
This script uses a varbinary(max) column and an insert of 170,000 bytes to deliberately exceed the default byte limit. Please note that while VARBINARY(MAX) is used in this example for simplicity, in a real-world scenario you should always choose the appropriate data type and length for your specific requirements.
USE AdventureWorks2022;
GO
-- 1. Enable CDC at the database level
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = DB_NAME() AND is_cdc_enabled = 1)
BEGIN
EXEC sys.sp_cdc_enable_db;
END
GO
-- 2. Create a simple table with a LOB column (varbinary(max))
IF OBJECT_ID('dbo.CdcLobDemo', 'U') IS NULL
BEGIN
CREATE TABLE dbo.CdcLobDemo
(
Id int IDENTITY(1,1) PRIMARY KEY,
Payload varbinary(max) NULL,
Note nvarchar(100) NULL
);
END
GO
-- 3. Enable CDC for the table and include the LOB column
IF NOT EXISTS (SELECT 1 FROM sys.tables t WHERE t.object_id = OBJECT_ID('dbo.CdcLobDemo') AND t.is_tracked_by_cdc = 1)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'CdcLobDemo',
@role_name = NULL,
@supports_net_changes = 1;
END
GO
-- 4. Produce the error by inserting a LOB > 64KB (~170KB)
DECLARE @bytes int = 170000;
-- Build a large varbinary(max) using a single byte (0x41) replicated @bytes times
DECLARE @chunk varbinary(max) = 0x41;
DECLARE @bigBlob varbinary(max) = CAST(REPLICATE(CONVERT(varchar(max), @chunk), @bytes) AS varbinary(max));
PRINT N'Attempting insert of ' + CAST(@bytes AS nvarchar(10)) + N' bytes...';
INSERT dbo.CdcLobDemo (Payload, Note)
VALUES (@bigBlob, N'Insert exceeding max text repl size');
GO
-- **This insert will fail with the LOB data size error.**
-- 5. Fix the configuration for the demo
-- The error message shows 170000 bytes. We need a value >= 170000.
DECLARE @requiredBytes bigint = 170000;
DECLARE @targetMaxReplSize bigint;
-- Compute a safe, minimal value (10% headroom)
SET @targetMaxReplSize = CEILING(@requiredBytes * 1.10); -- Target is 187,000.
-- We will round this up to 200,000 bytes (200KB) for this specific demo.
-- Apply configuration
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max text repl size', 200000;
RECONFIGURE;
-- Optional: Re-hide advanced options
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
-- 6. Rerun the insert
DECLARE @bytes int = 170000;
DECLARE @chunk varbinary(max) = 0x41;
DECLARE @bigBlob varbinary(max) = CAST(REPLICATE(CONVERT(varchar(max), @chunk), @bytes) AS varbinary(max));
PRINT N'Re-attempting insert of ' + CAST(@bytes AS nvarchar(10)) + N' bytes... (Should succeed)';
INSERT dbo.CdcLobDemo (Payload, Note)
VALUES (@bigBlob, N'Insert after configuration fix');
GO
SELECT * FROM dbo.CdcLobDemo;
SELECT * FROM cdc.dbo_CdcLobDemo_CT; -- Check the captured changes
GO
This issue acted as a valuable lesson for our team, to realise that even without transactional replication, we were still governed by its configuration limits. If you’re using CDC, it’s not a question of if you’ll hit this limit, but when your data grows large enough to reach it.
We hope this post and the accompanying code save you the investigation time we spent. If you’re running a database with LOB columns and CDC, take a moment to check your max text repl size now and avoid an unexpected failure during your next large data update! Keep in mind that if both replication and CDC are running concurrently, changing this value will impact both.
Did you run into this issue with a different feature? Let us know in the comments below