CDC is enabled, but cdc.dbo<table-name>_CT table is not being populated

Question

I have enabled CDC using the following steps:

exec sys.sp_cdc_enable_db;

exec sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'table_name',
@role_name = N'CDC_Access',
@supports_net_changes = 1;

I can see that a CT table has been created in the System Tables; SQL Server Agent is on, and I can see the cdc.db_name_capture job has been created and is running.

However, even though the table_name table is being populated, I never see anything in the CT table. I have other tables that have CDC enabled for them in the same database which are being updated, and CDC is capturing data for them and storing it in the CT table created for that specific table.

Why would this one table not be capturing data even though other tables are?

I read online that perhaps it has something to do with the transaction log becoming too large, but I still have plenty of drive space left (~2TB free).

What can I do to debug this issue?

Thank you so much, in advance! :)

Edit 1

Here is the output of exec sys.sp_cdc_help_change_data_capture. subscription_events is the table that I am having troubles with.

Edit 2

Here is the output of exec sys.sp_cdc_help_jobs;.

Edit 3

Here is the output of select * from sys.dm_cdc_log_scan_sessions;.

Here is the output of select * from sys.dm_cdc_errors;

Edit 4

Running select serverproperty('productversion') provides the following version number: 11.0.3401.0.

Habituate

Have you checked output of sys.sp_cdc_help_change_data_capture? Is it okay? – Eatable

I think it is okay. I ran exec sys.sp_cdc_change_data_capture and I see my new table in there, alongside the other tables that are functioning properly. I'll add the output of that row as an edit to my question. – Habituate

Ok then, are you sure there are changes occurring in this table? Also, there's default capture delay of 5 seconds, changes in source table may not be immediately visible in CDC table. But I assume you checked it several times during a period longer than 5 seconds. Right? :-) – Eatable

Yes, I have watched the subscription_events table grow in size (by going to Properties->Storage) :). I left the database in this state over the weekend, and the CT table had 0 rows, even though many thousands of rows were inserted over the weekend. This morning I disabled CDC on that table, and then re-enabled it, but the problem persists. – Habituate

Then try the jobs. Please post the result of exec sp_cdc_help_jobs – Eatable

Okay, I've added the output of that command. :) – Habituate

I wonder if other tables have current changes. Check that. Maybe a CDC log scan is in progress and none of the tables register changes? We have 3 DMVs: sys.dm_cdc_log_scan_sessions, sys.dm_repl_traninfo and sys.dm_cdc_errors. Please check them too. Especially the first one with end_time of NULL. – Eatable

I've added the output of sys.dm_cdc_log_scan_sessions and sys.dm_cdc_errors. The sys.dm_repl_traninfo table is huge, and I wasn't sure what information to look at. I've never used these tables before, so thank you for mentioning them to me; what am I looking for in here? – Habituate

Well, the errors :-) Things starting to become clearer now. Which version is your SQL Server? (select serverproperty('productversion') ). We might be witnessing a bug here. I think short term solution is to restart the capture job, but long term this may require installing a Cumulative Update. – Eatable

Also, I just checked some of the other CT tables, and the row count is continuing to grow. – Habituate

Running select serverproperty('productversion') provides the following version number: 11.0.3401.0. – Habituate

OK try to do the following: 1) stop the capture job; 2) run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1; EXEC sp_replflush; 3) close this query window in which you executed these commands; 4) start the capture job; 5) check the sys.dm_cdc_errors table for new rows and check if the changes starting to be visible. – Eatable

That seems to have worked! I am seeing the change data in the cdc.dbo_subscription_events_CT table! Thank you so much! :) So this is a known bug with SQL Server then? Also, if you add this as an answer, I will mark it as such. – Habituate

Also, the sys.dm_cdc_errors table is empty. – Habituate

No, that's not a known bug I suppose. There might be some obscure things that have happened in your database. Keep an eye on it in the future. – Eatable

Answer 1

Do the following:

1. Stop the capture job;
2. Run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1; EXEC sp_replflush;
3. Close this query window in which you executed these commands;
4. Start the capture job;
5. Check the sys.dm_cdc_errors table for new rows and check if the changes starting to be visible.

Eatable

Checking the sys.dm_cdc_errors table revealed that I didn't associate the files for the file group. Error message was "The filegroup 'CDC' has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added." – Archaeological

Had the same issue with the filegroup – Haslett

Had the same issue with the filegroup because the database was Always-On, but the disk for the additional filegroup didn't exist on the secondary. SQL Server lets me create the filegroup anyway on the primary and enable cdc on that database. At no point where there any errors or issues other than the CT tables not filling. Once I removed the database from AO, CDC began filling tables – Haslett