9

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.

enter image description here

Edit 2

Here is the output of exec sys.sp_cdc_help_jobs;.

enter image description here

Edit 3

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

enter image description here

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

enter image description here

Edit 4

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

FooBar
  • 384
  • 2
  • 4
  • 14
  • Have you checked output of `sys.sp_cdc_help_change_data_capture`? Is it okay? – Endrju May 19 '14 at 18:42
  • 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. – FooBar May 19 '14 at 18:48
  • 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? :-) – Endrju May 19 '14 at 18:57
  • 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. – FooBar May 19 '14 at 19:07
  • Then try the jobs. Please post the result of `exec sp_cdc_help_jobs` – Endrju May 19 '14 at 19:10
  • Okay, I've added the output of that command. :) – FooBar May 19 '14 at 19:13
  • 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. – Endrju May 19 '14 at 19:23
  • 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? – FooBar May 19 '14 at 19:34
  • 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. – Endrju May 19 '14 at 19:38
  • Also, I just checked some of the other CT tables, and the row count is continuing to grow. – FooBar May 19 '14 at 19:38
  • Running `select serverproperty('productversion')` provides the following version number: `11.0.3401.0`. – FooBar May 19 '14 at 19:40
  • 1
    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. – Endrju May 19 '14 at 19:47
  • 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. – FooBar May 19 '14 at 20:23
  • Also, the `sys.dm_cdc_errors` table is empty. – FooBar May 19 '14 at 20:30
  • 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. – Endrju May 19 '14 at 20:50

1 Answers1

16

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.
Endrju
  • 2,354
  • 16
  • 23
  • 1
    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." – manu97 Nov 20 '17 at 18:27
  • Had the same issue with the filegroup – Craig Efrein Mar 25 '22 at 10:28
  • 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 – Craig Efrein Mar 28 '22 at 12:59