Hey everyone 👋 I’m running into an issue with CDC in Airbyte using a Microsoft SQL Server source (Incremental | Append). Situation: • A table had 2 new columns added ~1 month ago • Columns are populated in the source table • In Airbyte, the columns are visible in the schema • But values are always

NULL

(even for newly inserted rows) Root cause (so far): • The original CDC capture instance (

cdc.dbo_your_table_CT

) does not include the new columns • DBA created a new capture instance (

cdc.dbo_your_table_N_CT

) which does include them • Now both capture instances exist in parallel Open questions: 1. How can I confirm which CDC capture instance Airbyte is actually using? 2. Will “Refresh stream” + keeping Incremental | Append make Airbyte switch to the new capture instance and also get a new full load of the table with data for the new columns while retaining the history?

Great question — this is a well-known pain point with MSSQL CDC and column additions. Let me address both questions based on what the Airbyte documentation covers. Root Cause Confirmation Your diagnosis is correct. When new columns are added to a table using

ALTER TABLE ... ADD

, the existing CDC capture instance does not automatically include them. The Airbyte docs explicitly state: > "Adding new columns to existing tables using

ALTER TABLE <table> ADD <column>

will not automatically be captured by the CDC stream. As a result, the column will be excluded from CDC tracking (while it might appear in the Schema section, it will return zero records)." [MSSQL Troubleshooting] This explains exactly why the columns are visible in Airbyte's schema but always return

NULL

. Question 1: Which capture instance is Airbyte using? The knowledge sources do not explicitly describe a way to inspect or configure which capture instance Airbyte selects when multiple exist in parallel. However, the recommended fix from the docs is to disable and re-enable CDC on the table (rather than creating a second capture instance alongside the old one), which results in a single, clean capture instance reflecting the updated schema:

-- Step 1: Disable CDC on the table
EXEC sys.sp_cdc_disable_table  
    @source_schema = N'<schema>',  
    @source_name   = N'<table>',  
    @capture_instance = N'<capture instance (typically schema_table)>'

-- Step 2: Re-enable CDC on the table
EXEC sys.sp_cdc_enable_table  
    @source_schema = N'<schema>',  
    @source_name   = N'<table>',  
    @role_name     = NULL

-- Step 3 (Optional): Validate captured columns
EXEC sys.sp_cdc_get_captured_columns   
    @capture_instance = N'<capture instance (typically schema_table)>';

[MSSQL Troubleshooting] > Note: If your DBA used a

@role_name

when originally enabling CDC, make sure to apply the same parameters when re-enabling it, otherwise the Airbyte user may lose access to the capture instance. [MSSQL Troubleshooting] Question 2: Will "Refresh Stream" + Incremental | Append work? Yes, a Refresh Stream is the correct action to take after resolving the capture instance issue. The Airbyte docs state that for new columns, you should: 1. Review and approve the schema changes in Airbyte. 2. Enable the newly added columns. 3. Navigate to the Status page, locate the stream, click the three dots (⋮), and choose Refresh Stream. [CDC Limitations] For

Incremental | Append

mode specifically, a Refresh and Retain Records option is available, which will re-sync the stream (getting a full load including the new column values) while retaining existing history in the destination. [Refreshes] Recommended Action Plan 1. Have your DBA disable the old capture instance and disable the new parallel one (

dbo_your_table_N_CT

), then re-enable CDC cleanly on the table using the SQL above. 2. In Airbyte, click Refresh source schema to detect the updated structure. 3. Approve the schema changes and enable the new columns. 4. Trigger a Refresh Stream → Retain Records to backfill the new column values while keeping history. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai What will happen if I Navigate to the Status page, locate the stream, click the three dots (⋮), and choose Refresh Stream. Will this refresh all streams or only the selected one?

Choosing Refresh Stream from the three-dot menu next to a specific stream will refresh only that selected stream, not all streams in the connection. This is confirmed by the Airbyte docs on per-stream actions: > "Clicking the three grey dots next to any stream opens the available options for the stream." [Connection Status] And the Refresh documentation notes that refreshes can be triggered "at either the stream or connection levels." [Refreshes] Additionally, the FAQ explicitly confirms stream-level independence: > "If I Refresh or Clear a single stream in a connection, does the generation ID increment for other streams in that connection? No. Streams within a connection have independent generation IDs. Clearing/Refreshing a single stream will only increment that stream's generation. Other streams are unaffected." [Refreshes FAQ] So in your case, triggering Refresh Stream on the affected stream will only re-sync that stream, leaving all other streams in the connection untouched. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai What will happen if we deactivate the existing cdc instance for the table which does not include the new columns and then enable the new cdc instance for the table with the new columns. Will we loose history?

The knowledge sources do not explicitly address what happens to the destination history in Airbyte when you swap CDC capture instances on the MSSQL side. However, here is what can be confirmed from the available documentation: What the Docs Say About the MSSQL Side Disabling the old capture instance and enabling a new one (or re-enabling CDC) is the recommended approach to ensure the new columns are tracked. The docs do not indicate this causes any data loss in the source table itself — it only affects which changes are tracked going forward in the CDC change table. [MSSQL Troubleshooting] What Happens on the Airbyte/Destination Side This is where you need to be careful. After changing the capture instance, you will need to trigger a Refresh Stream in Airbyte so it can snapshot the updated schema (including the new columns). The CDC docs state: > "If you create a new schema/column that is not yet being synced, it must first be snapshotted before CDC can begin tracking changes." [CDC Limitations] For

Incremental | Append

mode, a refresh re-syncs the stream with a full snapshot. Whether existing destination history is retained depends on the type of refresh you choose (Retain Records vs. Overwrite). The knowledge sources do not go into further detail on this specific MSSQL capture instance swap scenario. What the Docs Do NOT Cover The knowledge sources do not explicitly state whether swapping capture instances (rather than disabling/re-enabling CDC as a single operation) causes Airbyte to lose its LSN position or triggers any unintended behavior. I would recommend: 1. Following the exact disable + re-enable pattern recommended in the docs (single clean capture instance). 2. Using Refresh Stream → Retain Records afterward to backfill new column values without losing destination history. For a definitive answer on LSN continuity across capture instance swaps, I'd suggest consulting the Airbyte MSSQL connector documentation or opening a support ticket, as this specific scenario is not covered in the available knowledge sources. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Can I try to run a refresh stream (+retain records) for this table with the second new CDC instance (including the new columns) first to see if it picks up the new CDC instance?

The knowledge sources do not explicitly describe how Airbyte selects between multiple parallel CDC capture instances on MSSQL, nor do they confirm whether a Refresh Stream + Retain Records would cause Airbyte to "pick up" the new capture instance without first resolving the duplicate instance situation on the database side. What the docs do clearly state is: 1. The root cause of NULL values is that the old capture instance doesn't include the new columns — and the recommended fix is to disable the old instance and re-enable CDC cleanly, not to run parallel instances. [MSSQL Troubleshooting] 2. A Refresh Stream triggers a new snapshot of the source table. Even if the snapshot picks up the new columns correctly (because the source table has the data), CDC tracking going forward still depends on which capture instance Airbyte reads from. If it continues reading from the old instance, newly inserted rows will still return NULL for the new columns after the refresh. [CDC Limitations] 3. Retain Records will preserve your existing destination history during the refresh. [Refreshes] So while the Refresh Stream + Retain Records is safe from a history-preservation standpoint, it may not fully solve the problem if the underlying capture instance issue isn't resolved first. The knowledge sources don't provide enough detail to confirm whether this "test" approach would work reliably. I'd recommend resolving the capture instance on the DB side first as documented, to avoid a second refresh cycle. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai If there a 2 CDC instances available for a table, the existing one and a new one. Which one will Airbyte choose for CDC?

The knowledge sources do not contain any information about how Airbyte selects between multiple parallel CDC capture instances when more than one exists for a table in MSSQL. This specific behavior is not documented in the available materials. What the docs do recommend is avoiding the parallel instance situation altogether by following the disable + re-enable pattern, which results in a single, clean capture instance: > "We recommend disabling and re-enabling CDC on the table. This will create a new capture instance that reflects the updated structure and includes the new column." [MSSQL Troubleshooting] For a definitive answer on which capture instance Airbyte picks when two exist in parallel, I'd recommend checking the Airbyte MSSQL connector documentation directly or reaching out to Airbyte support, as this is not covered in the available knowledge sources. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai What will happen if I not refresh the stream but Airbyte reads from the new CDC instance?

The knowledge sources do not explicitly describe what happens if Airbyte starts reading from a new CDC capture instance without a stream refresh being triggered first. However, based on what the docs do say, here are the relevant confirmed points: 1. New columns must be snapshotted before CDC can track them. The docs state: "If you create a new schema/column that is not yet being synced, it must first be snapshotted before CDC can begin tracking changes."[CDC Limitations]This means that even if Airbyte were to read from the new capture instance (which includes the new columns), newly inserted or updated rows would still show NULL for those columns in the destination until a proper snapshot (refresh) has been performed to backfill them. 2. The initial snapshot is a full SELECT. The first sync for any new schema/column is effectively a full refresh via a

SELECT

statement. Without triggering this, Airbyte has no baseline values for the new columns in the destination. [CDC] 3. Schema changes detected without a refresh may cause issues. The docs recommend enabling

Approve all schema changes myself

to prevent newly detected columns from being included in the sync without a proper snapshot, specifically to reduce the risk of LSN issues and sync failures. [CDC Limitations] In short: skipping the Refresh Stream step is likely to result in continued NULL values for the new columns, even if Airbyte is reading from the correct capture instance. A refresh/snapshot is a required step to properly initialize the new columns in the destination. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai The new CDC instance was created and currently there are 2 parallel CDC instances for one of the tables. Tonight the sync for the MS SQL Server connection failed fully. The error is as follows: "The sync encountered an unexpected error in the change event producer and has stopped. Please check the logs for details and troubleshoot accordingly. Some streams were unsuccessful due to a source error. See logs for details.Something went wrong within the source connector."

The knowledge sources do not contain specific information about this exact error message ("The sync encountered an unexpected error in the change event producer") in the context of parallel CDC capture instances on MSSQL. However, based on what the docs do cover, there are a few relevant points: What May Be Contributing to the Failure Parallel capture instances are not a supported/recommended configuration. The Airbyte docs explicitly recommend the disable + re-enable approach to get a single clean capture instance, rather than running two in parallel. The presence of two capture instances may be causing unexpected behavior in how the connector resolves LSN ranges. LSN validation issues with multiple capture instances are a known problem. A recent bug fix (PR #77668) specifically addressed a scenario where having multiple capture instances — some of which the Airbyte user lacks permissions on — caused

MIN(sys.fn_cdc_get_min_lsn())

to return zero, triggering false LSN validation failures and breaking CDC syncs entirely. [LSN validation fix] This is directly relevant to your situation: if the Airbyte database user does not have access to the new capture instance (e.g., because it was created with a

@role_name

gating role), the LSN validation could fail and cause the sync to abort. [LSN validation fix] Recommended Next Steps 1. Check the detailed logs for LSN-related errors or permission errors on the capture instances. 2. Resolve the parallel instance situation as soon as possible by following the recommended approach — disable both capture instances and re-enable CDC cleanly on the table:

-- Disable old capture instance
EXEC sys.sp_cdc_disable_table  
    @source_schema = N'<schema>',  
    @source_name   = N'<table>',  
    @capture_instance = N'<old_capture_instance>'

-- Disable new capture instance
EXEC sys.sp_cdc_disable_table  
    @source_schema = N'<schema>',  
    @source_name   = N'<table>',  
    @capture_instance = N'<new_capture_instance>'

-- Re-enable CDC cleanly (with all new columns)
EXEC sys.sp_cdc_enable_table  
    @source_schema = N'<schema>',  
    @source_name   = N'<table>',  
    @role_name     = NULL

[MSSQL Troubleshooting] 3. After resolving the capture instance, trigger a Refresh Stream → Retain Records in Airbyte to re-snapshot the table with the new columns. The parallel capture instance situation is very likely the root cause of tonight's failure. Resolving it on the database side first is the critical step. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.