Questions tagged [change-data-capture]
Questions under this tag are expected to relate to Change Data Capture technologies available in many RDBMS server products. Change data capture is designed to capture insert, update, and delete activity applied to Server tables, and to make the details of the changes available in an easily consumed relational format.
157 questions
1
vote
0
answers
24
views
Table partitioning for CDC enabled table
Is there any issue you have faced enabling table partitioning for CDC enabled tables?
I have done some quick testing and table partitioning for CDC enabled tables is working well in terms of switching ...
- 95
2
votes
1
answer
147
views
Unable to alter CDC tables
I have an issue with being unable to alter a CDC table that previously caused us no issue.
We run alter statements on our cdc tables to add a column to the tables that we now are starting to face an ...
- 23
-1
votes
1
answer
50
views
Bug in how CDC jobs record run_duration in sysjobhistory
Normally SQL Agent job history is recorded in msdb.dbo.sysjobhistory with run_duration in the format DD:MM:HH:SS, -- so for example a value of 105 means 1 minute and 5 seconds.
However the CDC capture ...
- 662
0
votes
0
answers
15
views
is it a good idea to use GTID for tenant meta data?
i want To build an audit log for tracking user activity using Kafka Connect CDC (Change Data Capture) and MySQL, and set a custom GTID (Global Transaction Identifier) before each transaction, here’s a ...
- 101
0
votes
1
answer
97
views
_CT records with identical __$seqval
There is a CDC enabled table of the following form:
create table dbo.foo (
id int not null primary key,
a char(1),
b char(1),
c char(1),
d char(1),
e char(1),
updated ...
- 5,001
0
votes
0
answers
21
views
RDS Mysql replica cdc performance
I have a RDS Mysql which has high read/write volume. ~10k IOPS on an average and peaks up to 16k IOPS.
I have another RDS mysql replica of the main. We are using this replica to capture cdc logs and ...
0
votes
1
answer
115
views
CDC cleanup job -- history
On SQL Server 2019, I enabled CDC on some tables about 2 days ago. The CDC cleanup job is scheduled to run every day and the retention period is 3 days.
When I checked the job history of the cleanup ...
- 1
0
votes
0
answers
99
views
SQL Server: Querying Change Data Tables directly?
I am messing around with Change Data Capture on SQL Server.
Documentation for table containing the change data (cdc.<capture_instance>_CT) says:
We recommend that you do not query the system ...
- 1,277
0
votes
1
answer
24
views
Do old inserts get treated as new CDC events during node bootstrap?
When a new Cassandra node bootstraps and receives data from existing tables for which Change Data Capture (CDC) is enabled, do old inserts get treated as new CDC events? I don't see any reference to ...
- 5
1
vote
1
answer
131
views
CDC Capture job fails with 'Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index'
When the CDC Capture job runs, it fails with this error:
Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index.
This index is a SQL Server CDC created index ...
- 21
6
votes
1
answer
285
views
Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working
I have a two-node SQL Server 2019 Availability Group with a database that has CDC enabled for several tables. The CDC is used to keep track of transactions that have not been captured for population ...
- 70.5k
0
votes
0
answers
127
views
invalidating slot "a" because its restart_lsn 6093/6BB6F1B0 exceeds max_slot_wal_keep_size
we use google cloud sql (version is PostgreSQL 15.5), the config as follows:
config, and max_slot_wal_keep_size = -1, but slot always become lost wal status (restart LSN become null) when running for ...
qiang wang
1
vote
1
answer
200
views
Anyone using CDC and having performance issues extracting net_changes? We have 8 tables tracked with CDC "@support_net_changes=1"
So 8 tables are being tracked with CDC "@support_net_changes=1". 7 of them are ok in regards to performance. As some of these tables are only 10-40 million rows and data doesn't change ...
- 111
0
votes
0
answers
37
views
Modify CDC Schema In SQL Server
Can we add a column in CDC captured tables?
Basically we are going to process CDC rows and we want to tracked which rows are already processed. So we are thinking about adding a bit field to the CDC ...
0
votes
0
answers
51
views
CLEANUP and CAPTURE jobs not being created when CDC is enabled in an AOAG setup
We are in the process of upgrading to ENTERPRISE SQL SERVER 2022. The DB is configured with AOAG. When we enable CDC at the database/table level, for some reason, the CLEANUP and CAPTURE jobs are not ...
- 1
0
votes
1
answer
38
views
Clarifications on Maintenance jobs & CDC on upgrade to ENTERPRISE EDITION
Raising these on behalf of our DBA team. We are looking at moving to ENTERPRISE SQL SERVER 2022 soon and would need help clarifying a few things:
We are looking at a 3-NODE setup:
(1) NODE-1: Primary
(...
- 1
0
votes
1
answer
46
views
How i can get changes in DB postgresql without triggers
I have database, and in this database ~10 users changes rows, how i can get this changes and user who changed, without triggers. I tried read WAL, and there no info about who what change.
0
votes
1
answer
166
views
CDC cleanup doesn't work even when using sp_cdc_cleanup_change_table which completes successfully
I'm running the query below to find CDC tables with data beyond the configured retention depth:
SELECT DB_ID() AS database_id, ct.capture_instance, object_id, source_object_id
, rcounts....
- 483
1
vote
0
answers
254
views
SQL Server - CDC - Log-Scan Process failed to construct a replicated command from log sequence number (LSN)
CDC is enabled on the database and multiple tables are included for cdc. We had been receiving the below alert intermittently, monthly once which is causing the changes not written on the CT tables. ...
- 29
1
vote
0
answers
38
views
On demand snapshot and CDC from Postgresql multitenant DB
I have a Postgresql multitenant DB where each table contains tenant level metadata. e.g.
Table "connector"
Column | Type |...
- 111
0
votes
0
answers
15
views
Incremental CDC from Postgresql for select rows
I have a postgresql DB which has tables containing multi-tenant data. Example:
Table Customer
CustId
Cust Name
First
CustA
Second
CustB
Table CustomerShoppingDet
Item Name
CustId.
Buying Date
...
- 111
0
votes
1
answer
135
views
How do you correlate SQL Database Audit spec's log column transaction_id with a LSN? from trans log or CDC capture table?
How do you correlate SQL Database Audit log column "transaction_id" with a LSN either from the trans log or a CDC capture table?
I am trying to do this because cdc only captures the data ...
- 1
1
vote
0
answers
90
views
Cleanup job for CDC failed
The clean up job for CDC failed with this error:
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe ...
- 231
1
vote
1
answer
625
views
CDC is not working after restoring the databse
I have just restored my database using the below script,
USE [master]
GO
RESTORE DATABASE [MyDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSTANDARD\MSSQL\Backup\mydb.bak' WITH ...
0
votes
1
answer
702
views
What is the meaning of lsn_time_mapping.start_lsn
I have enable Change Data Capture on a table. I'm trying to understand cdc.lsn_time_mapping. Unfortunately the documentation is vague and missing a column from the results.
What's the difference ...
- 25.1k
0
votes
1
answer
90
views
Correlating SQL Audit transaction_id to LSN
I have a relatively simple question that is "Google resistant".
I have a SQL Database Audit spec that logs a transaction_id. I also have a Change Data Capture (CDC) spec that logs __$seqval (...
- 117
5
votes
2
answers
401
views
Can I change the owner of the cdc schema?
I've enabled Change Data Capture (CDC) on a database using exec sys.sp_cdc_enable_db
This creates a new cdc schema which is owned by the cdc user
select
s.*
, dp.name
from sys.schemas as s
...
- 5,512
1
vote
2
answers
143
views
Commit logs under cdc_raw folder getting corrupted
I am using cassandra 3.11.12 version. I have recently enabled cdc_raw in cassandra configuration and created new key space and table with WITH cdc=true; and after inserting values ran the alter ...
- 19
1
vote
1
answer
446
views
CDC Error - NULL Source Schema and Source Table - Error msg on both enable and disable table
Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error:
Could not delete change table entries made obsolete ...
- 11
1
vote
0
answers
66
views
DR - Missing binlogs in Maxwell
We have a standard db.r6g.2xlarge RDS MySQL instance running in production with 2 replicas. Maxwell CDC is setup to publish binlogs to a Kafka topic.
From a RPO point of view: in the worst case (say ...
- 111
1
vote
1
answer
984
views
Oracle query taking a very long time to execute
I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical ...
- 69
2
votes
1
answer
806
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41
1
vote
1
answer
3k
views
SQL Server DB Log keeps growing / CDC enabled database
I've enabled change data capture on a SQL Server database.
The database is in full recovery mode.
The LOG backups of the database are being done hourly.
The capture jobs was running without any issues ...
- 243
2
votes
1
answer
1k
views
Merging data from identical tables from multiple databases into one larger table. N-1 table replication
There are N databases with identical table structures in a multitenant, multi-database system. The desire is to replicate a table(s) from these databases into a single larger table in a OLAP database, ...
- 629
0
votes
1
answer
252
views
Can we keep change-tracking on a replicated table going after changing the distribution database?
We have an MSSQL replicated table which has change-tracking set up on the subscribed table.
The change tracking is used to send changes into Snowflake.
However - now we need to reconfigure replication ...
- 133
0
votes
0
answers
77
views
Resuming incremental SSIS CDC sync with big CDC "backlog"
We're using an SSIS package which syncs changes from one (main) database to another (reporting) using CDC. The package runs periodically syncing the data in small increments.
After having an issue ...
- 119
-1
votes
1
answer
804
views
sp_rename column and CDC
I have a table in a SQL Server 2019 instance which is being CDC tracked.
However when I want to rename a column it failes with the error:
Cannot alter column 'Columnn2' because it is 'REPLICATED.
I ...
- 2,482
0
votes
2
answers
184
views
postgres 11.3 checksum validation post migration
I am in the process of migrating our AWS RDS postgresql from one account to another with zero downtime. I am going with pg_dump/pg_restore + AWS CDC for ongoing replication.
I am looking for a way to ...
- 101
2
votes
0
answers
371
views
Sensor data from Postgres to Redis
I have a Postgres database with a table that is updated with new data every 5 minutes from a weather station.
Now, as soon as the table is updated with a new record from the sensor, I would like this ...
- 21
1
vote
1
answer
782
views
CDC - I cannot disable that on the second instance
I have change data capture enabled for most of the tables in one of my databases. Database is hosted on SQL Server instance with version 2016.
I have some testing to be done, so I wanted to restore ...
- 192
0
votes
1
answer
2k
views
how to read from cdc tables?
I wanted to try out the change data capture feature of sql server, but I cannot get it to work
What I did
-- enable cdc
EXEC sys.sp_cdc_enable_db
-- enable it on a table, for testing
EXEC sys....
- 139
0
votes
1
answer
125
views
SQL Server modification timestamps
I'm implementing ingestion job from SQL Server 2016. For performance reasons we want to implement incremental loads.
Few facts:
Customer has read replica as a part of always on availability group ...
- 101
1
vote
1
answer
288
views
Create Audit Trail from SQL CDC
I have a few tables on which I have enabled CDC. As each row in CDC table contains all the columns regardless if they were changed or not, so it becomes difficult to figure out only the changed data. ...
- 21
0
votes
1
answer
317
views
Do we need additional license to dump goldengate trail logs to kafka?
We have a Oracle (12c) GoldenGate license only (we do not have an oracle goldengate for big data).
We need to transfer the gg trail logs to kafka then to hdfs.
Do we need to buy the oracle goldengate ...
- 3
0
votes
2
answers
1k
views
Auditing via change data capture (CDC) for MSSQL
I want to ask the DBA community about best practices for auditing the MSSQL database. I set up CDC MSSQL and experience some difficulties with it. It's working all fine until I change the structure of ...
- 27
1
vote
1
answer
1k
views
insert and update triggers do not fire on replicated table
I have created a publication and a subscription on two servers with wal_level = logical in my postgresql.conf. Replication is functioning normally. My problem is to track changes in the replicated ...
- 113
0
votes
0
answers
76
views
SQL Server 2014 any way to suppress KB3030352?
I'm upgrading from SQL Server 2008 R2 to SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1.
Compatibility mode is set to 120. Trace Flag 9481 is enabled. The problem still is present in lower ...
0
votes
1
answer
193
views
Is the WAL data text format documented anywhere?
Looking around the Web, I can find plenty of information explaining how to set up WAL replication, and how to get the changes with pg_logical_slot_get_changes or pg_logical_slot_peek_changes. This ...
- 212
0
votes
0
answers
406
views
MSSQL CDC for views and stored procedures
is it possible to enable CDC for Views or Stored procedures?
BR,
Val
- 27
1
vote
2
answers
822
views
Identify and delete specific data from Usertable_CT captured from a CDC enabled table
Scenario
We recently performed a heavy update operation updating two columns on a 2 tb user table. The User table is CDC enabled, which caused Usertable_CT table to bulk up. The User table is a heavy ...
- 11