Sql-server – CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4

Based on my experience with such issue and as mentioned in This Blogs.msdn article

You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE). The release notes also documents the fixes.

So I suggest to suppress regression you enable it.

Trace flag 9481 solved your problem because if forced SQL Server to use old optimizer. So basically you are using SQL Server 2014 but not new CE which comes with it.

Trace flag 4199 makes sure the optimizer is using all changes/fixes made to SQL Server optimizer since SQL Server 2005.

From this support article

Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL setting will be used enable trace flag 4199-related hotfixes on-by-default. This article describes the mechanics and policy of how plan-affecting hotfixes will be delivered for SQL Server 2016 and later versions.

There are three ways to do it depending on need.

1. By enabling the trace flag in a batch (by using DBCC TRACEON command) right before the target query, and then disabling the trace flag (by using DBCC TRACEOFF command) right after the query.

2. Starting with Microsoft SQL Server 2005 Service Pack 2 (SP2) and Microsoft SQL Server 2008, the query-level option "QUERYTRACEON" is available. This option lets you to enable a plan-affecting trace flag only during single-query compilation.

3. You enable it in starup parameters.

The first and second point will only make sure query/batch runs with 4199 trace flag while third point makes sure any query which runs sees this trace flag enabled.

NOTE: Enabling trace flag requires sysadmin permission so if you ask your developer to use it in query for instance he might not be able to assuming developers have limited access.

I would suggest you to use querytraceon or dbcc traceon to see if queries are performing up to mark.

The more common and accepted way is that once a job lands on a server in an AG you start editing it:

• Add a first job step that checks whether this is the primary.
• Exit on success if it isn't.
• Otherwise continue onto the normal job step.

This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find them):

• How to run SQL Job only on primary replica in AlwaysOn Availability Groups?

• SQL Server Agent Jobs and Availability Groups

The second way is to create a watchdog job using similar methods which will toggle the jobs on and off. This is probably more acceptable seeing as you're fiddling with SQL Server internal jobs. But it's up to you.

I wasn't quite happy with the samples above hardcoding AG names and such. I prefer to just have the job step operate within the database in question, and this way it can work out for itself if it even has a group and is part of it (as your jobs should continue running even if you temporarily remove them from an AG).

Set Nocount On

If  Exists (
    -- Database in an AG
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name = Db_Name()
    )
And Not Exists (
    -- Database in an AG which is Primary on this instance
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name =  Db_Name()
    And     Upper(dhags.primary_replica) = Upper(@@Servername)
    )
Begin
Raiserror('This is not the primary replica.', 16, 1) With Nowait
Return
End

-- Check if the database isn't accessible
If  Not Exists (
    Select  Top 1 0
    From    sys.databases
    Where   databases.name = Db_Name()
    And     state_desc = 'ONLINE'
    )
Begin
    Raiserror('The database doesn''t exist or isn''t ONLINE on this node.', 16, 1) With Nowait
    Return
End

This kind of thing is also useful for SSIS jobs and other bits that pop up over time.

Going one step further you probably want to create a PowerShell script which will go through and check your servers from time to time for AGs which have CDC jobs and which don't have a watchdog, so that you will be notified to create one.