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.

Filter by
Sorted by
Tagged with
0
votes
1answer
386 views

CDC error - Error converting data type varchar to uniqueidentifier

I have CDC and Replication configured on an Always On cluster. I had to patch the SQL Server on Node 1 so I failed over the instance to node 2 and started patching node1. After about an hour one of ...
0
votes
0answers
42 views

Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints. We decided on the archival since the ...
6
votes
1answer
949 views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
2
votes
1answer
46 views

Which are the downsides of not deleting the CDC records in change tables?

If I do something like this: USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 52494800; -- (100 years) GO The records in the change ...
0
votes
1answer
62 views

Change Data Capture in MySQL/PHP by writing to a MySQL table (rather than streaming to Kafka/Rabbit/etc)?

I have a small internal web app I'm developing with PHP and MySQL. I'm looking to sync client webpages to a server by requesting all changes since a certain date. I've found a number of solutions ...
5
votes
5answers
13k views

Does MySQL have a version of Change Data Capture?

We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until ...
0
votes
1answer
127 views

Where does Attunity's CDC services write error data?

We use Attunity's CDC services to move data from Oracle to SQL Server. Using the Oracle CDC Designer snap-in tool, I can see the current status of the service and an error message if a problem has ...
0
votes
1answer
150 views

Empty CDC scans on highly active SQL Server

We have a SQL Server which has highly active databases which will have number of transactions in an hour. CDC is enabled on this database to carry forward the committed changes to another server. But ...
1
vote
1answer
117 views

How to make cdc skip the point that it is stuck at?

I have an issue where the transnational log has grown to its max size. Running fn_dblog I noticed that its complaining that a drop was done. It looks like cdc is stuck there. The description column in ...
0
votes
1answer
31 views

SQL Server CDC (Change Data Capture) Existing Data Seed

I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are ...
0
votes
0answers
68 views

Can MSSQL CDC be used to capture old committed transactions?

I can successfully capture change data from mssql CDC by issuing the query cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' ) Here, from_lsn is the ...
26
votes
4answers
15k views

When to use CDC to track history?

SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table. Through the use of special table value functions (TVF) ...
2
votes
1answer
649 views

Monitoring CDC (Change Data Capture) performance

We currently have in place CDC, DW team is reading data, everyone happy. Then we needed to add couple new columns. Easy peasy following this answer... well, actually we had some hiccups but that's ...
2
votes
0answers
138 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
1
vote
1answer
65 views

Error on CDC enabled Table

I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message. "...
1
vote
1answer
291 views

Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC

What is a preferable way to move ETL data into Data Warehouse? Create/Update date on the OLTP application, or Change Data Capture (CDC)? Given the two are allowed on a system and resourcing is not ...
4
votes
2answers
2k views

CDC with AlwaysOn Availability Groups

So I just recently got to enable CDC on an always on AG. While many sources claimed enabling CDC on alwaysON AG is as easy as it is on any standalone server, turns out it really isn't. As MS says here:...
1
vote
1answer
2k views

SQL Fastest Way to find Data Difference Between Two Tables

I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that ...
0
votes
1answer
58 views

Change Data Capture SSIS not picking changes

I am trying to implement change data capture in SQL Server 2012. I enabled it at database and table level. when i try to update the table, CDC_CT captures the changes. However when i am using SSIS CDC ...
1
vote
0answers
163 views

SQL Server 2016 CDC (change data capture) missed data issue

I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains ...
4
votes
2answers
3k views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
3
votes
0answers
362 views

Msg 22830 level 16 on sp_cdc_enable_db_internal - could not update metadata that indicates database is enabled for Change Data Capture

I'm attempting to create an MCVE for this recent question. SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database. ...
4
votes
2answers
136 views

SQL Server Change Data Capture order of records

Does anybody know if change data capture functions return always data in the order of occurence?. In a CDC system is quite important to get the data modifications in the order they have been generated....
1
vote
1answer
287 views

CDC Capture Instance or State to ensure referential integrity

I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database. As an example, I have INVENTORY_TRANSACTION in one ...
7
votes
2answers
2k views

What tables are affected by Change Data Capture

We're looking at using Change Data Capture in SQL 2012. We're trying to work out if there is an easy way to find out what tables have been changed by a given lsn. Is there an inbuilt function, or ...
2
votes
1answer
291 views

Cannot disable CDC - sp_cdc_disable_db_internal gives error 8152: 'String or binary data would be truncated'?

Trying to disable CDC and getting this error: Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 264 Could not update the metadata that indicates database … is not enabled ...
2
votes
1answer
43 views

How can I SELECT an 'instance' in PostgreSQL?

I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code: CREATE TRIGGER cdc_table ON db.table AS ...
7
votes
1answer
5k views

SQL Server 2016: Temporal table performance vs. Triggers and CDC

It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended ...
3
votes
1answer
253 views

Change Data Capture Use Case?

I'm working on a custom application that needs to determine changes to a particular column, on particular rows in our database (MS SQL 2008R2). When the row is created a created & modify ...
3
votes
1answer
443 views

Change Data Capture not running when Mirror is Suspended

We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are ...
1
vote
2answers
581 views

Find changes made in the table in sql server 2012

I am using a SQL Server.I have more 1000 tables in a database which i want to track for data changes.I have read about CDC(Change Data Capture) and will enable it on all the 1000 tables.It creates ...
2
votes
2answers
3k views

CDC vs replication vs anything else

In my product we need to create a solution for the reporting team to extract and process the data. We need to send them incremental data changes from our DB. I am evaluating various solutions. Looked ...
7
votes
1answer
3k views

CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4

We had an issue with CDC where the capture job failed and wouldn't restart itself. It was in a DEV environment so no harm but has anyone seen these errors or know what exactly went wrong with CDC? ...
1
vote
1answer
138 views

Data Warehouse how normalized for tracking metadata changes?

I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be ...
1
vote
1answer
29 views

Monitoring changes on a View

Many DBMS implement views. However, do some of them implement change data capture on views (rather than actual tables), in a near real time fashion ?
0
votes
1answer
984 views

Restore Error: Could not upgrade the metadata

I am trying to restore a database from a Prod environment (11.0.6020.0) to a lower environment server (11.0.3128.0). I realize the lower has an older SP, we are working on correcting that now. The ...
2
votes
2answers
115 views

Use Netbackup 7.7 `WITH KEEP_CDC`

I'm using SQL Server Change Data Capture in a production database. To my surprise, I found that when restoring that database to a lower environment the CDC data is not kept—all the tables, functions, ...
1
vote
1answer
602 views

Change Data Capture for Asynchronous Secondary Replica

MSDN states the following in Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for SQL Server 2014: Limitations and Restrictions: Change tracking and change data ...
1
vote
0answers
687 views

Sql server 2012 log file growth of cdc enbled db

I have CDC enabled on a SQL Server 2012 database that is a primary DB in transactional replication and the primary in a availability group. The log file is a little over 95 GB, a historic high. I have ...
2
votes
2answers
4k views

Change Data Capture vs trigger for logging changes

I am going to create history table which is populated by trigger (after insert, update, delete). As only 20% of the columns are going to be updated, I decided to log only the changed values - if the ...
9
votes
1answer
1k views

How to tell if a backup file contains CDC?

Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine ...
2
votes
2answers
5k views

How to retrieve the Capture Instance for a given table?

Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled. I am trying ...
1
vote
0answers
522 views

Capturing table changes with CDC and In Memory tables

We had a plan to capture table changes with CDC until we realized that it doesn't support In Memory tables. Is there another way (preferably as straightforward as CDC) to capture data changes (from ...
2
votes
0answers
397 views

SQL Server 2012 CDC not working on certain tables only

I am using SQL 2012 sp3. I have CDC working on many tables and DBs without issue. Last night a change was put into the system and 2 of my CDC'd tables were dropped and modified without properly ...
1
vote
0answers
26 views

Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different SQL instances?

I want to distribute Oracle data changes to the same table name in 2 different SQL instances, on 2 different SQL servers. Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different ...
1
vote
0answers
60 views

Incremental load of full database

Please anyone suggest best approach for incremental load of all the tables in a database to a data warehouse , I have already developed SSIS package to do that using CDC, but enabling CDC for 100 ...
1
vote
1answer
293 views

Which parameters and metrics to look at when CDC based replication is causing high load?

I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while ...
2
votes
2answers
2k views

Best way to capture historical changes between levels in a dimensional hierarchy

What is the cleanest way to capture changes between levels in a dimensional hierarchy? I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region ...
1
vote
0answers
99 views

Lookback Query utilizing Change Data Capture in SQL Server 2014? [duplicate]

I'm using SQL Server 2014 to maintain a small database and while looking for a way to keep a History of my Data I came upon the Change Data Capture Mechanism. My question now is if there is an easy ...
8
votes
3answers
6k views

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...