New Bug: Change Data Capture (CDC) Fails after ALTER COLUMN

[read this post on Mr. Fox SQL blog]

EDIT #1: Fri 19 Jun 2015
We received confirmation the Microsoft Support Team the bug can be reproduced – however at this point in time they have confirmed that there will be NO FIX


EDIT #2: Thurs 02 Jun 2016 
Bug reproduced in
SQL Server 2016 (GA release).


EDIT #3: Sat 21 Oct 2017
Bug reproduced in SQL Server 2017 (GA release) (Windows)
(As at writing CDC is not supported on SQL 2017 Linux)

Unfortunately this issue now affects all SQL Server versions and patch levels from SQL Server 2008 to the current GA version.  


Please read below for methods to identify the issue and a work around.

This week we discovered a new SQL Bug that affects all SQL Versions from SQL Server 2008 to SQL Server 2016 (GA Release).  The bug specifically affects the SQL Enterprise feature of Change Data Capture (CDC).  The bug is difficult to identify – however the article below outlines a method to replicate the bug, and a method to remediate it.

If you are not familiar with CDC then have a look here

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.

Could not locate text information records for the column "MyColumn", ID 13 during command construction. [SQLSTATE 42000] (Error 18773)
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0038a7d9:000172d4:0010}.
Back up the publication database and contact Customer Support Services. [SQLSTATE 42000] (Error 18805)
Log Scan process failed in processing log records.

I have provided a SQL Script at the end of this post which you can use to replicate the error.

As of writing this post there is no fix yet available for this error.  I will post here again once it becomes available and how you can get it.

And so, lets get into the nitty gritty of how to reproduce and fix the error.

Continue reading