CDC (Change Data Capture) is not capturing data on SQL 2014

Question

Hi ,

I need some help on CDC. I've enabled CDC on database on few tables. Previously it was running fine. After deleting the capture job and recreating , it stops working.
I couldn't find any errors as well.looks all blank.

I tried below steps as well:

-Stop the capture job;
-run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @TIME = 0, @EGCC = 1; EXEC sp_replflush;
-Check the sys.dm_cdc_errors table for new rows but no rows ..

Could you help me on this?

Answer 1

-run EXEC sp_repl done @xactid = NULL

Guess why the SP name contains "repl": Because it's for replication, not for CDC.
Setup CDC with SSMS GUI from scretch to get it working again.

Answer 2

Hi @Kumari, Meenakshy ,
You can use the following order to see if you have set the database successfully:
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
You can use the following order to see if you have set the table successfully:
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;

By the way you may set the CDC by the following order:
USE MyDB
GO
EXECUTE sys.sp_cdc_enable_db;
GO

Best regards
Jong

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

Thanks for the reply.

I've recreated CDC jobs. I can see two jobs are running cdc.apo16t_capture and cdc.apo16t_cleanup jobs but still can't see any data capture. I query one of CT table, select * from cdc.po_requisitioner_*****_CT table and can't see any data capture.

CDC has been enabled for following tables.

select * from cdc.change_tables;

Also, checked SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;

Is something I'm missing here?

Thx.

Answer 4

Hi Pan,

Thanks for the prompt reply.

I've successfully ran the query ALTER AUTHORIZATION ON DATABASE::[MyDB] TO [sa].. Also SQL Agent job is already running.

I can see the jobs history ,looks all good to me. Also, checked for any errors using query select * from sys.dm_cdc_errors; but all blank with no rows.

Answer 5

Hi @Kumari, Meenakshy ,
WOW...then I may give you some notice things that may need to pay attention:

· SQL Server version must be 2008 or above;

· Memory-optimized tables (a feature of SQL Server 2014 or later) cannot be used at the same time. Otherwise, the following error occurs:

· @@ SERVERNAME and serverproperty ( 'servername') must be consistent (the properties of the local server name and the server instance must be consistent). The follow script adjusts that two to be consistent. If the latter two are still inconsistent, the SQL Server service needs to be restarted.

if serverproperty('servername') <> @@servername
begin
declare @Testta sysname
set @Testta = @@servername
exec sp_dropserver@Testta =@Testta
set @Testta = cast(serverproperty('servername') as sysname)
exec sp_addserver@Testta = @Testta , @rayn = 'LOCAL'
PRINT 'ok'
end

select @@SERVERNAME,serverproperty('servername')

· The SQL Sever Agent service must be turned on. The CDC function must be implemented by a job.

· Cannot use TRUNCATE TABLE for tables with CDC enabled. You can disable and truncate before enabling CDC.

· If the structure of the table changes, a forced conversion will occur in the capture instance table: a new addition cannot be captured, a deleted column remains NULL, and a modified column type. To be on the safe side, disable the capture instance and then enable it.

· When querying CDC-related tables, it is recommended to add With (NOLOCK), otherwise it is easy to cause blocking or deadlock.

· A table can have at most two capture instances.
(I hope this can give you some help)