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

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

EDIT: Thurs 02 Jun 2016 – I have reproduced the bug in SQL Server 2016 (GA release) so unfortunately it now affects all SQL Server versions and all patch levels from SQL Server 2008 to the current 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.

Continue reading

Rebuild a Standard Table to a Partitioned Table

I have been working on a SQL DW database and revisiting an existing SQL table partitioning scheme.  The main reasons we’re going to all this trouble is to (a) implement a full partition based optimisation process and (b) implement a partial database backup scheme. (I will blog about these later!)

The SQL DW database has grown significantly and some of the tables are large (2.1+ billion rows) so nearly everything you do with it takes time, needs to be considered and tested.  The data is not a candidate for archive and therefore the partition sliding window technique is not being considered.

The partitioning includes several major components of work (and can be linked below);

  1. partitioning large existing non-partitioned tables
  2. measuring performance impacts of partitioned aligned indexes
  3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
  4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
  5. implementing partial backups and restores (via leveraging partitions)
  6. implementing partition aware index optimisation procedures
  7. Calculating table partition sizes in advance

This first blog post deals with partitioning large existing non-partitioned tables. I will blog about the other steps later.

Continue reading