7

We had an issue with CDC where the capture job failed and wouldn't restart itself. It was in a DEV environment so no harm but has anyone seen these errors or know what exactly went wrong with CDC?

SQL 2014 EE 12.0.5540 - 2 node AG with SSISDB

enter image description here

Procedure or function sp_batchinsert_1663605265 has too many arguments specified. For more information query the sys.dm_cdc_errors dynamic management view

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00002d0d:0000f11f:0002}. Back up the publication database and contact Customer Support Services. For more information

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

We attempted to drop and recreate the capture job using sp_cdc_drop_job abd sp_cdc_add_job. We also attempted failing over to the other node as well as server/service restarts. Nothing seemed to let CDC restart itself.

We applied CU4 on Friday through AG rolling patching so we are on the latest and greatest. This issue cropped up right after we patched and failed over.

Added Log query result

enter image description here

Improve this question
14
  • Can you read the LSN? I've seen weird things happen with log corruption and or transient (or not so transient) disk issues. Basically the error is saying it failed to properly read and get the data from that lsn in the log.
    – Sean Gallardy
    Commented Feb 28, 2017 at 3:11
  • @SeanGallardy I'm going to get the systems team to pull the log backups when we saw the first occurrence (directly after AG failover to the new patched secondary). FWIW, we are on a compellent SAN that just had the controller OSes upgraded 2 weekends ago. We saw some read retries, but no hard I/O errors (823-825). They claimed to have resolved that issue a/o last week. I definitely want to follow up on this and see what exactly happened. If it can happen in DEV, it can happen in PROD.
    – Kris Gruttemeyer
    Commented Feb 28, 2017 at 4:08
  • @SeanGallardy I believe the added picture is what we are looking for. Does this align with what you've seen before?
    – Kris Gruttemeyer
    Commented Feb 28, 2017 at 13:48
  • What is the compatibility level of the database experiencing the issue ?
    – Kin Shah
    Commented Feb 28, 2017 at 14:46
  • 120, it's always been that as this was a new build so it didn't come from a previous version or anything.
    – Kris Gruttemeyer
    Commented Feb 28, 2017 at 14:48

1 Answer 1

Reset to default
6

It looks like CU2+SP4 (12.0.5540) changes the structure of the underlying _CT system tables that get generated. It adds the column [__$command_id] int null.

When you upgrade in place to the new CU, the capture jobs will start failing or go to 'between retries...' with the following errors:

enter image description here enter image description here enter image description here

Dropping and recreating the capture job does not work (using cdc_add_job). I had to drop the capture agent CDC job, disable CDC (sp_cdc_disable_table) on the table and then re-enable CDC (sp_cdc_enable_table), which re-adds the job and successfully fires the job and begins collecting.

The issue is that, upon dropping and re-enabling CDC, you lose the previously existing _CT table and all of it's data.

The only information I ahve found about CU4 changing CDC is the following from MS: https://support.microsoft.com/en-us/help/3030352

It says that they fixed the ordering issue, but do not list any structural changes to the CDC system tables.

UPDATE 1: I've opened a paid support case with MS regarding this issue. There's also a connect item open for it: https://connect.microsoft.com/SQLServer/Feedback/Details/3130381

UPDATE 2: Per MS, using the command sp_vupgrade_replication after patching to CU4 will allow the capture agent job to start successfully. It still doesn't address the jobs failing before the command is run. It also does not address this added column not being documented anywhere. Still waiting to see what the final verdict is.

UPDATE 3: MS has officially posted to their blog and recognized this as an issue: https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

No word yet on if they will be re-releasing the CU4 patch to not require manual intervention to restart the CDC jobs or officially document the column change.

Improve this answer
4
  • I know this is an old(ish) thread now, but we are still have issues here. Upgrading to SSDT2017 resolves the issue, but that isn't something we can just undertake currently. MS claim that it is fixed in 2016 SP1CU5, 2014 SP2CU7 and 2012 SP4 (support.microsoft.com/en-gb/help/4053693) but having applied the update to a test server I still get the 'invalid column name __$command_id'. Is this something you are still battling with?
    – Simon Hellings
    Commented Jan 18, 2018 at 16:36
  • We modified our packages to account for the new column. That fix you listed above from MS remedies the issue of CDC jobs not coming back up after patching. The additional column will be a part of CDC going forward so you'll either have to stay where you are or account for the additional column in your packages.
    – Kris Gruttemeyer
    Commented Jan 18, 2018 at 16:38
  • Thanks Kris; super quick response... OK - now for the really dumb question... How can I modify my packages to do handle this? I just filled in the boxes and hit preview? Not having the column mapped still caused the error.
    – Simon Hellings
    Commented Jan 18, 2018 at 16:49
  • I'm guessing you'll have to either add the column to your CDC source in the package and either carry it through or map it to nothing.
    – Kris Gruttemeyer
    Commented Jan 18, 2018 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.