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

Comments (0)

Skip to main content