SQL Server CDC Troubleshooting
  • 3 Minutes to read
  • Dark
    Light
  • PDF

SQL Server CDC Troubleshooting

  • Dark
    Light
  • PDF

Article Summary

No Data Had Been Captured Regarding Some Tables in the CDC Tables

In this case, there are some reasons to make sure the CDC works correctly in your SQL Server. 

Check if your table(s) enabled for CDC

In SQL Server, the CDC mechanism creates a job, and a Change Tracking (CT) table for each table separately. 

That means, there's a need to enable any table you want to be capture by the CDC jobs. 

Therefore, please refer to our docs on enabling databases and tables in order to make sure the table had been enabled to CDC.

In order to check if the table is enabled for CDC and track changes, please use the next 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 if your database has CDC jobs enabled and SQL Server Agent is running

First check that SQL server agent is running by selecting it with right-click:


If the database is enabled in CDC and the tables are enabled on CDC and you still can't see new logs coming in, check that the SQL agent for CDC is enabled for capture logs and clean logs. You can do so by downloading Microsoft SQL Server Management Studio, connecting to your data SQL server and underneath:

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

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

where {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 should see enabled jobs. 




Start the SQL Server Agent on Azure SQL Server Virtual Machine  

In case your SQL Server is running on Azure SQL Server Virtual Machine, there are some cases the SQL Server Agent is needed to be started from the instance itself.
In order to make it, please follow the next steps:

  1. Go 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.

Update Capture Tables After a Schema Change

Rivery is unable to emit data change events for the table as long as the capture table uses the outdated schema. To allow Rivery to resume processing change events, you must refresh the capture table by performing the following steps:

1. Disable your SQL Server's CDC River. Set the toggle to false 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>';

4. Disable the current instance of CDC:

EXEC sys.sp_cdc_disable_table @source_schema = [<schema>], @source_name   = [<table>], @capture_instance = [<capture_instance>];

Please 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 Rivery.

5. 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 Rivery username.

6. Remove the previous CDC instance. Make sure that the table has only one CDC instance.

7. Configure a new SQL Server CDC River and ensure that the Source table is visible in the schema.


Was this article helpful?