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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 |
