Skip to main content
Feedback

SQL server CDC troubleshooting

Troubleshooting: When no data is captured in CDC tables

Check your table(s) is enabled 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 to be captured by the CDC jobs. To learn more, refer to Enabling Databases and Tables to ensure the table is enabled for CDC.

To check if the table is enabled 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 the database is enabled in CDC, the tables are enabled on CDC, and you cannot view new logs coming in, check that the SQL agent for CDC is enabled to capture logs and clean logs. 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 to be tracked by the CDC mechanism in SQL Server, as required by SQL Server.
  • 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 doesn't exist for this table. Reinitialize the table data. Please refer to our docs: Page - 'SQL Server Change Tracking', Section - 'Cleanup During River Extraction'.

Solution

To address this issue:

  1. Check for min_valid_version=0 or missing last_sync_version:

    • Verify if the issue is caused by min_valid_version being zero or if last_sync_version is not received.
    • If there is no minimum valid version or zero, this error is shown, indicating the need to reinitialize the table. Without the minimum valid version, change tracking data cannot be retrieved.

  2. Investigate cleanup processes:

    • The problem could be due to the cleanup process, which is automated and might occur frequently or rarely depending on various 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 river, and check the logs after the next run when the error message is raised.

  4. Analyze change tracking table usage:

    • Understand the usage of the change tracking tables, as re-initializations happen automatically and could be triggered by multiple factors.
    • 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 version column is updated or deleted frequently.

  6. Address concurrency issues:

    • If multiple processes are trying to synchronize with change tracking simultaneously, there could be concurrency issues.
    • Ensure the synchronization process is coordinated correctly to avoid conflicts if the table is used by multiple rivers.

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

In case your SQL Server is running on an Azure SQL Server Virtual Machine, there are some cases in which the SQL Server Agent must be started 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 Change Data Capture is enabled for a SQL Server table, event records are persisted to a capture table on the server as changes are made to the table. If you change the structure of the Source table, for example, by adding a new column, the capture table is not dynamically updated.

More information on managing changes to the Source table can be found in Microsoft's documentation.

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 River. 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 obtaining 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, no new data should be inserted, and writing into the table should be stopped; the old data will be saved by Data Integration.
  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 River 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