Skip to main content
Feedback

SQL server CDC troubleshooting

Troubleshooting: When CDC tables capture no data

Check that you have enabled your table(s) for CDC

In SQL Server, the CDC mechanism creates a job and a Change Tracking (CT) table for each table separately. You must enable any table you want the CDC jobs to capture. To learn more, refer to Enabling Databases and Tables to ensure you have enabled the table for CDC.

To check if you have enabled the table for CDC and track changes, use the command: -- The command should return results.

EXEC sys.sp_cdc_help_change_data_capture
GO

And search the table schema and table name under the _source_schema _and _source_name _fields _.

Check your database has CDC jobs enabled and SQL Server Agent is running

Check the SQL server agent is running using right-click:

If you have enabled the database in CDC, enabled the tables on CDC, and cannot view new logs coming in, check that the SQL agent for CDC has capture and cleanup jobs enabled. You can download Microsoft SQL Server Management Studio, connecting to your data SQL Server:

{HOSTNAME} > SQL Server Agent > Jobs > cdc.{DATABASE}_capture

{HOSTNAME} > SQL Server Agent > Jobs > cdc.{DATABASE}_cleanup

{HOSTNAME} is your connection's hostname (or the name of the connection you chose), and {DATABASE} is the name of the database you set up for CDC. You can view the enabled jobs.

note
  • A CDC table must have a Primary Key because the CDC mechanism in SQL Server requires it to track the table.
  • Data Integration does not support the @index_name option.

Change tracking issue failing after first run, successful even after doing ‘initiate migration'

Problem

Error: [RVR-MSSQL-RDBMS-301]: Last synchronization version does not exist for this table. Reinitialize the table data. Please refer to our docs: Page - 'SQL Server Change Tracking', Section - 'Cleanup During Data Flow Extraction'.

Solution

To address this issue:

  1. Check for min_valid_version=0 or missing last_sync_version:

    • Verify if min_valid_version being zero or the absence of last_sync_version causes the issue.
    • If there is no minimum valid version or it equals zero, this error appears, indicating the need to reinitialize the table. Without the minimum valid version, Data Integration cannot retrieve change tracking data.

  2. Investigate cleanup processes:

    • The cleanup process, which runs automatically, might cause this problem and can occur frequently or rarely depending on several factors.
    • Frequent cleanup might remove change tracking data too often, leading to synchronization issues.

  3. Review logs:

    • Examine MSSQL server logs for any messages related to change tracking.
    • Look for logs about specific issues encountered during the cleanup process or change tracking synchronization.
    • Reinitialize the table, run the Data Flow, and check the logs after the next run when the system raises the error message.

  4. Analyze change tracking table usage:

    • Understand the usage of the change tracking tables, as multiple factors can trigger automatic re-initializations.
    • Determine the reason for this issue.

  5. Table update/delete frequency:

    • Frequent updates or deletions without corresponding inserts might lead to frequent cleanups, causing change tracking to malfunction.
    • Check if the table undergoes many updates or deletions.
    • Assess if the application updates or deletes the version column frequently.

  6. Address concurrency issues:

    • If multiple processes are trying to synchronize with change tracking simultaneously, there could be concurrency issues.
    • Ensure you coordinate the synchronization process correctly to avoid conflicts if multiple Data Flows use the table.

Starting the SQL Server agent on an Azure SQL Server virtual machine

If your SQL Server is running on an Azure SQL Server Virtual Machine, you may need to start the SQL Server Agent from the instance itself.

Procedure

  1. Navigate to Azure portal and search for SQL Server Virtual Machine.
  2. Click on the right SQL server name you want to start the agent on.
  3. On the SQL Server machine, under the overview, click on the virtual machine name.
  4. In the virtual machine that opened, click on Run Command > RunPowerShellScript:
  5. Run the next script and wait for its end:

    Start-Service SQLSERVERAGENT

Managing source table changes

Use case

SQL Server Schema Change migrates DDL (Data Definition Language) but not data.

Overview

When you enable Change Data Capture for a SQL Server table, the server persists event records to a capture table as changes occur. If you change the structure of the Source table, for example, by adding a new column, the capture table does not dynamically update.

For more information on managing changes to the Source table, refer to the Microsoft - Handling changes to source table topic.

Updating capture tables after a schema change

Data Integration is unable to emit data change events for the table as long as the capture table uses the outdated schema. To enable Data Integration to resume processing change events, you must refresh the capture table.

Procedure

  1. Disable your SQL Server's CDC Data Flow. Turn off the toggle, and then click Disable Stream and Schedule.
  2. Add a new column in your source database.
  3. Create a new CDC instance for the table containing the new column by retrieving the name of the table's capture instance containing the new column:
note

<schema> and <table> are the schema and table that contain the new column.

EXEC sys.sp_cdc_help_change_data_capture @source_schema = '<schema>', @source_name = '<table>';

  1. Disable the current instance of CDC: EXEC sys.sp_cdc_disable_table @source_schema = [<schema>], @source_name = [<table>], @capture_instance = [<capture_instance>];
note
  • <schema> and <table> are the schema and table that contain the new column, and [< capture instance>] is the name of the capture instance for the table containing the added column.
  • During this process, do not insert new data, and stop writing to the table; Data Integration will save the old data.
  1. Create a new CDC instance: EXEC sys.sp_cdc_enable_table @source_schema = [<schema>], @source_name = [<table>], @role_name = [<username>];
note

<schema> and <table> are the schema and table that contain the new column, and [< username>] is the Data Integration username.

  1. Remove the previous CDC instance. Make sure that the table has only one CDC instance.
  2. Configure a new SQL Server CDC Data Flow and ensure that the Source table is visible in the schema.

Current stream position in your database

To confirm the stream position, run the following command on the server:

-- For the latest LSN in the transaction log
SELECT TOP 1
[Current LSN]
FROM
sys.fn_dblog(NULL, NULL)
ORDER BY
[Current LSN] DESC;
On this Page