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.

There is no issue in capturing nvarchar(max) column for the CDC. In fact, I just did it practically before writing this and did not find any issue.

Though you have already mentioned that you did troubleshoot by disabling/enabling CDC. So, a particular case that I have seen, does not fit in your situation, However I would like to explore that as an possible cause. That is, if database was restored from different server where it had CDC enabled? because that can result error message what you have encountered. Which can be resolved by using KEEP_CDC option. Please refer: http://msdn.microsoft.com/en-us/library/cc645938.aspx 

Now coming back to your troubleshooting steps:

Did you disable CDC at database level and still seeing error? If you have not done it then you may want to try.

  @ Mohan Kumar =There is no issue in capturing nvarchar(max) column for the CDC. In fact, I just did it practically before writing this and did not find any issue. 

i agree with you Mohan, But Problem was with altering schema column from ntext to nvarchar(max) ,When CDC capture will be running, You'll definitely get this error in SS 2008R2 RTM.

however for time being, i solved it by Disabling the CDC at Table Level & database level. Then Again Enable it ,

CDC capture is running smooth now.

Just came across this one.  Slightly old thread, but absolutely still 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/