Hi Everyone..,

my sql server version is 2008r2 RTM package running on Windows machine 2008R2

I have enabled CDC on a Database with selected tables, it was perfectly working fine,today due to some reason i converted Ntext column to Nvarchar(max) using Alter command , the same it got reflected to CDC table.

After that i inserted some records into that Changed schema table but couldn't get reflected into CDC table.for reason then i checked up with CDC_capture Job 'View History' and getting the following error.

Following is the Error Log :

10/30/2013 18:24:17,cdc.Live_ProdDB_CDC_capture,In Progress,,,cdc.Live_ProdDB_CDC_capture,,,In progress,00:09:38.8330000,,,,,,

10/30/2013

18:23:08,cdc.Live_ProdDB_CDC_capture,Retry,2,HOME\SQL08R2,cdc.Live_ProdDB_CDC_capture,Change Data Capture Collection Agent,,Executed as user: NT AUTHORITY\SYSTEM. Could not locate text information records for the column "ResumeConvertedText"<c/> ID 29 during command construction.[SQLSTATE 42000] (Error 18773)  The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0019e6fb:0000091b:01b9}. Back up the publication database and contact Customer Support Services. [SQLSTATE 42000] (Error 18805)  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. [SQLSTATE 42000] (Error 22859),00:00:01,16,22859,,,,9

10/30/2013

18:14:04,cdc.Live_ProdDB_CDC_capture,Error,2,HOME\SQL08R2,cdc.Live_ProdDB_CDC_capture,Change Data Capture Collection Agent,,Msg 22859<c/> Level 16<c/> State 2<c/> 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. For more information<c/> query the sys.dm_cdc_errors dynamic management view.,00:00:00,16,22859,,,,0

10/30/2013

18:22:08,cdc.Live_ProdDB_CDC_capture,Retry,2,HOME\SQL08R2,cdc.Live_ProdDB_CDC_capture,Change Data Capture Collection Agent,,Executed as user: NT AUTHORITY\SYSTEM. Could not locate text information records for the column "ResumeConvertedText"<c/> ID 29 during command construction. [SQLSTATE 42000] (Error 18773)  The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0019e6fb:0000091b:01b9}. Back up the publication database and contact Customer Support Services. [SQLSTATE 42000] (Error 18805) 

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. [SQLSTATE 42000] (Error

22859),00:00:00,16,22859,,,,8

10/30/2013

18:14:04,cdc.Live_ProdDB_CDC_capture,Error,2,HOME\SQL08R2,cdc.Live_ProdDB_CDC_capture,Change Data Capture Collection Agent,,Msg 22859<c/> Level 16<c/> State 2<c/> 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. For more information<c/> query the sys.dm_cdc_errors dynamic management view.,00:00:00,16,22859,,,,0

10/30/2013

Following things i troubleshooted :

1.Disable & enabled CDC_capture.

2.restarted sql server agent. (running fine)

Can anyone tell me is there problem in capturing Nvarchar(max) columns for the CDC. and please suggest me some ways and

Thanks in advance & highly appreciated with feedback.

Hi,

I'm getting the same error. Were you able to resolve it?

Thanks.

Was digging around and came across this thread - Slightly old but relevant.

You have in fact hit a CDC bug in SQL that affects all SQL Versions from SQL 2008 to the latest patch level on SQL 2014.

The bug will occur when you change a data type from TEXT to VARCHAR(MAX) on a table that is marked for CDC, and you then update any row to push the LoB value off page (ie total row size exceeds the page size).  What makes this bug so damn sinister is that you could successfully do the table ALTER and then the problem will sit dormant till whenever you update that LoB column off page.

When the bug occurs it will manifest itself with a broken CDC Log Reader (ie SQL Agent CDC Capture Job) that will NOT move past a specific LSN.

There is no fix from Microsoft.  The only way to fix it is to disable the table for CDC, and re-enable

As FYI - I have blogged about it here with a script to reproduce it.

https://mrfoxsql.wordpress.com/2015/05/02/new-bug-change-data-capture-cdc-fails-after-alter-column/