December 14, 2013 at 12:11 am
Recently I Restored a database from Live to Test Instance for implementing CDC feature on sql server 2008 X64 Enterprise Edition on Windows server 2008r2 X64.
***Meant there was NO CDC Enabled in Production Nor Replication Features Its stand Alone.
Initially i Enabled CDC on Database level and next for 3 Tables, after that i inserted records for all the tables but the same was not getting reflected to CDC tables. then i checked in agent service-> CDC capture Job -> view history, then i got the following below error..
Message
Msg 22859, Level 16, State 2, 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, query the
sys.dm_cdc_errors dynamic management view.
Date 14-Dec-13 8:07:35 AM
Log Job History (cdc.HC_UAT_DB_V4_capture)
Step ID 2
Server ADDCSVHC05
Job Name cdc.HC_UAT_DB_V4_capture
Step Name Change Data Capture Collection Agent
Duration 00:34:29
Sql Severity 16
Sql Message ID 22859
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 11
Message
Executed as user: Test\hcuat. Could not locate text information records for the column "ExMaritalStatus",
ID 189 during command construction. [SQLSTATE 42000] (Error 18773) The Log-Scan Process failed to construct a
replicated command from log sequence number (LSN) {00232390:00001fc9:0015}. 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)
can anyone please help me.., what is the cause for the below error and how i can get rid of this.
"ExMaritalStatus",column -> Nvarchar datatype
Note :
*** issue can be solved by disabling & enabling CDC at database level but i need the root cause, again CDC capture job fails for capturing other Nvarchar columns when i Re-Enable at the database level. This cause is getting cycled/looping in capturing other colums.
Thanks for the advance.
May 13, 2015 at 6:58 am
Just happen to come across this - its an old thread but it seems that its still relevant.
You mentioned NTEXT and then NVARCHAR for your column - so it could be the same issue.
Long short this sounds like the bug we hit about 2 weeks ago which we reported to Microsoft
It affects all SQL Versions from SQL 2008 to the latest patch level on SQL 2014. The bug specifically affects the SQL Enterprise feature of Change Data Capture (CDC).
I blogged about it here - https://mrfoxsql.wordpress.com/2015/05/02/new-bug-change-data-capture-cdc-fails-after-alter-column/
In Summary - 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.
If its the same issue - sorry but there are no fixes (yet) - but yes the workaround you mentioned will work temporarily till the next column type change that is.
We are waiting on a hotfix.
Thanks - Rolf
https://mrfoxsql.wordpress.com
----------------------------------------
Rolf T (Mr. Fox SQL)
https://mrfoxsql.wordpress.com/
rolf.tesmer@mrfoxsql.com.au
----------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply