CDC may fail when assign a large size (more than 8000) value to the column

–by Liwei Yin, Microsoft SQL China Support

Assume you have CDC enabled for a table with text/ntext column.

1 You moidfy the colum type from text/ntext to varchar(max)/nvarchar(max)

2 Then you do an update to the column thereon, tried to assign a large size (more than 8000)value to the column, the CDC may fail and raise below error message:

You get below error message.

Could not locate text information records for the column “Col1”, ID 2 during command construction.

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00000033:00000068:0013}. Back up the publication database and contact Customer Support Services.

Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems.

Work around:

1 Remove CDC feature from all table with TEXT/NTEXT columns

2 Modify all TEXT/NTEXT to VARCHAR(MAX)/NVARCHAR(MAX)

3 Enable CDC again on these tables.

Reproduce steps, you can try below step in SQL Server 2014/2012

create database BreakCDCDB1 
go 
use BreakCDCDB1 
go 
-- Create Your Database 
CREATE TABLE CDCTest 
( 
ID int identity(1,1) 
,Col1 text 
) 
INSERT INTO CDCTest VALUES ('1 TEST') 
go 
sys.sp_cdc_enable_db 
GO 
-- enable table for CDC capture 
exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'CDCTest', @role_name = NULL 
go 
ALTER TABLE CDCTest ALTER COLUMN Col1 VARCHAR(MAX) 
go 
UPDATE CDCTest SET Col1 =Col1 + REPLICATE('X',8000) 
go 
select * from sys.dm_cdc_errors