Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [change-tracking]

Questions about tracking changes to the data in a database.

Filter by
Sorted by
Tagged with
0 votes
2 answers
99 views

Tool for monitoring changes to data in a database

I regularly back up my MariaDB databases using mariadb-dump (mariadb's mysqldump). However, I want to ensure data integrity in either of these cases: a vulnerability in an application using a ...
kataba's user avatar
  • 33
1 vote
0 answers
42 views

Change Tracking sometimes returns new inserts without primary key values

We utilize Change Tracking feature to catch inserts and updates for couple of tables. Queries are like this: SELECT P.* ,CT.SYS_CHANGE_OPERATION ,TC.commit_time FROM schema.tablename ...
Markku's user avatar
  • 11
0 votes
1 answer
69 views

Should I worry about a serial update execution plan?

For a mature .Net project I support we're currently exploring options to remove Entity Framework Core from the project while still maintaining some of the EF functionality we rely on. At the top of ...
Adrian's user avatar
  • 103
0 votes
1 answer
74 views

Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH

I would like to create transaction which will : DISABLE change tracking IF exists. TRUNCATE partition for table. SWITCH PARTITION from different table to main table. ENABLE change tracking IF was ...
adam.g's user avatar
  • 355
0 votes
1 answer
482 views

What permission have to set for SQL user for configuration of change tracking in Database?

I am building application which have to have option to configure (disable/enable) change tracking on tables on specific database. I am connecting to database via SQL user. Which database permissions ...
adam.g's user avatar
  • 355
3 votes
1 answer
500 views

SYS_CHANGE_OPERATION not showing correct value when row is Updated in CHANGE TRACKING

When a row is updated the SYS_CHANGE_OPERATION column is still showing I (Insert) and not U (Update) when querying CHANGETABLE . Here's what I'm doing. USE master GO CREATE DATABASE TestCT ; GO ...
Geezer's user avatar
  • 431
2 votes
2 answers
299 views

Change Tracking SYS_CHANGE_COLUMNS on Update operation

I would like to get an understanding of why on an update operation in the change tracking table, the SYS_CHANGE_COLUMNS can be NULL. I would expect the SYS_CHANGE_COLUMNS will be populated. In the ...
cenko's user avatar
  • 31
3 votes
1 answer
664 views

What are the performance implications of using checksums vs change tracking for SQL Server

Back in the day, an implementation with checksum_agg was made to check if "something changed" in a table in a SQL Server table, to then make updates in another database. The users want to ...
Ola Eldøy's user avatar
0 votes
0 answers
139 views

Change tracking, transaction log out of space and recovery mode change

I observed quite strange behavior of change tracking that I'm not able to explain. Few unusual things happened: DB was running fine, we read change tracking information as usually, up to version A. ...
Lukas.Navratil's user avatar
1 vote
1 answer
160 views

How does Wikipedia store article diffs (or any site which contains lots of text diffs per record)?

Looking at the Obama diffs on Wikipedia article history, there are probably thousands of diffs. Clicking on a specific older diff took about 3-5 seconds to load, as opposed to the more snappy ...
Lance's user avatar
  • 209
0 votes
0 answers
234 views

Change tracking after reboot

We have 2 SQL Server servers (Microsoft SQL Server 2014 12.0.6433.1 Enterprise Edition and Microsoft SQL Server 2016 Standard) that have been logging change tracking procedure failed with too many ...
Debbie's user avatar
  • 1
0 votes
1 answer
958 views

Is there any option in change tracking to track changes of specific columns of table?

Do you know If is possible to track changes (CHANGE TRACKING or CDC) with 'Track columns updated' but only specific columns which we would like to track. The reason is that we have table with 300 ...
grochu's user avatar
  • 45
4 votes
1 answer
3k views

Finding timestamp when index was created or reindexed

Is there any way to find when an index was last created or reindexed in PostgreSQL 12-13? The docs show a few standard views like pg_stat_user_indexes that track usage statistics for indexes, but I ...
Cerin's user avatar
  • 1,365
0 votes
1 answer
209 views

Get sequence value date?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated. All the tables & triggers in ...
User1974's user avatar
  • 1,467
4 votes
2 answers
2k views

How to "succinctly" detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, ...
André Christoffer Andersen's user avatar
-2 votes
5 answers
3k views

Challenge to SELECT only new records that were added since the last SELECT [closed]

Sometime ago I ran across a question/problem that I misunderstood as the user was not very skilled and I went ahead and solved it in the most ugly, cthullu, chaotic way possible. Now, if this is not ...
Vladislav Zalesak's user avatar
1 vote
1 answer
479 views

intra-query parallel thread deadlocks with only Page Locks with snapshot isolation and change tracking

I've been trying to optimise a data loading process. This is on Sqlserver 2016 (13.0.5850.14 - latest SP&CU). I've switched to change tracking for internal database changes and set tablocks on all ...
Brett's user avatar
  • 111
0 votes
3 answers
1k views

What are best practices for storing many iterations of a product in an RDBMS?

We are building a tool to track the prices of products over time, and using Postgres as our RDBMS. It is important that product attributes can be changed, and that the history of an product's ...
partytax's user avatar
1 vote
1 answer
356 views

Validating SQL Server Change Tracking Synchronization

I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
Davide De Simone's user avatar
0 votes
0 answers
411 views

Sql service broker and SqlDependency

I have a system where my TCP/IP command prompt application receive data and insert that data in [database1].[mytable]. Now I want to move that data from [database1].[mytable] to [database2].[...
deepak's user avatar
  • 101
0 votes
0 answers
250 views

Hash matches with collisions

I'm trying to improve certain matches that happen often, on many many dimension columns. So my choices as I understand are: Match on many columns Hash the columns, store the hash, match on that. If ...
Jamie Marshall's user avatar
7 votes
1 answer
997 views

Change tracking causing latch contention

I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
Danielle Paquette-Harvey's user avatar
1 vote
0 answers
515 views

Can you update statistics on a sys change tracking table

I'm tracking down some poor query times doing the typical query to get changed records: select StoreItemId from changetable(changes [dbo].[StoreItem], @clientChangeTrackingVs) as ChangedRow where [...
tcarvin's user avatar
  • 173
7 votes
2 answers
987 views

How to make sure change_tracking statistics stays updated

I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
Danielle Paquette-Harvey's user avatar
2 votes
0 answers
408 views

Performance drop after change tracking & isolation snapshot

Several days ago, I enabled change tracking on a db and some of its tables. A few days ago, I also SET ALLOW_SNAPSHOT_ISOLATION ON. (Probably important note: I never SET READ_COMMITTED_SNAPSHOT ON, ...
George Menoutis's user avatar
2 votes
1 answer
507 views

How to "update" change_tracking setting to enable TRACK_COLUMNS_UPDATED

I have a table where I have enabled change tracking without TRACK_COLUMNS_UPDATED. I know this because if I go through SSMS's Generate scripts, I get the line: ALTER TABLE [dbo].[dt] ENABLE ...
George Menoutis's user avatar
1 vote
2 answers
5k views

PostgreSQL Trigger to keep track of table changes

I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach: CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS $BODY$ BEGIN IF TG_OP = '...
Lisdengard's user avatar
31 votes
4 answers
51k views

Track all modifications to a PostgreSQL table

We have a table which has only roughly 500 rows, but it is very important for us. I want see all changes which happen to this table. The changes should get tracked with a timestamp. I don't want the ...
guettli's user avatar
  • 1,521
3 votes
0 answers
316 views

Change Tracking generating enormous transaction logs

I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min. Using the following command I'm tried to examine the contents of the log: SELECT Operation, ...
Mark Henry's user avatar
3 votes
1 answer
1k views

MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab'

I have a server that is failing to backup due to an error with the syscommittab and have gone through everything in this question: SQL Server 2008 unable to backup due to Failed to flush the commit ...
Jon Reeves's user avatar
4 votes
4 answers
925 views

Best practices for transferring data across long, wide tables

As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns. ...
Krishn's user avatar
  • 373
1 vote
1 answer
350 views

Why does my INSERT fail on SELECT permissions when I have change tracking enabled?

I have a user in a SQL Server 2014 database that only has permissions to insert into a table (no select permissions). When change tracking is enabled on the table, the inserts will fail with The ...
geofftnz's user avatar
  • 582
0 votes
0 answers
455 views

Good practices for audit data changes for SQL Server 2016

In my current project we are required to keep the records of data changes for the Employee table at least for specific field for NOW (it can be extended for all the fields later). We are also ...
user6460880's user avatar
7 votes
2 answers
9k views

Persistent Severity 016 alerts: "Change Tracking autocleanup is blocked on side table"

We had a large database running on a SQL Server 2014 Enterprise instance (evaluation license). Due to cost constraints, we had to move it to a new SQL Server 2017 Standard instance (per-core license; ...
Ross Presser's user avatar
0 votes
1 answer
221 views

Track MySQL Procedure Changes in MySQL

We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did ...
Nawaz Sohail's user avatar
  • 1,450
1 vote
3 answers
94 views

How to keep records of past week visits in a web app?

I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is MariaDB. Each time a visitor requests an article, the visits field of the article ...
Babr's user avatar
  • 113
1 vote
0 answers
1k views

CHANGETABLE query too slow

I've got a table with the following DDL: CREATE TABLE dbo.SubscriberProduct (SubscriberId INT NOT NULL , ProductId INT NOT NULL , PRIMARY KEY (SubscriberId, ProductId)); GO I've then enabled ...
Evaldas Buinauskas's user avatar
1 vote
1 answer
282 views

Is it safe to make a Unique Index the Primary Key?

I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined. The tables do have Unique Indexes but they are not defined ...
Don McCall's user avatar
3 votes
1 answer
530 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 ...
DDulla's user avatar
  • 141
2 votes
1 answer
1k views

Incremental sync for Sql Server

The Change Tracking feature of Sql Server allows to identify the modified rows of a modified database copy. This is obviously helpful for sync jobs, as the database can then be synced incrementally. ...
John's user avatar
  • 765
3 votes
1 answer
416 views

Is it possible to have change tracking ONLY on a replication server, not original?

I am working on a system where we are pulling records from a replication server and need to know when changes have occurred. We would like to not have to touch the original server and only read from ...
Matthew Czarnek's user avatar
2 votes
1 answer
737 views

Identifying field changes between tables and consecutive rows

I need to build an audit report to identify field changes between two tables, as well as between consecutive rows in one of the tables. The first table (data) holds the current data: id data_id ...
Steve H.'s user avatar
6 votes
0 answers
700 views

CHANGE_TRACKING_CURRENT_VERSION() incorrect on database in standby

We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run ...
LeonG's user avatar
  • 161
1 vote
1 answer
639 views

SQL Server 2012 Data Change Tracking

We currently have a front end application that is business critical and uses Microsoft SQL Server as the database provider. I have been asked to create another system that mimics some of the functions ...
Recnats's user avatar
  • 111
6 votes
1 answer
2k views

using Change Tracking functions from another database

If I turn on change tracking on a database called Production, can I use the functions to get the change data from another database? For example: CHANGE_TRACKING_CURRENT_VERSION(); I have tried the ...
SeanOB's user avatar
  • 163
1 vote
0 answers
150 views

Tracking Changes

I have an SSRS report with hierarchies for the company and their contact info. As these hierarchies change overtime, is there a way to track what has changed since the last run(run on a weekly basis)? ...
Utrillion's user avatar
4 votes
1 answer
2k views

Automatically detect table name in MSSQL Server database using stored function

I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes. In the past, changes have been made manually, but I'd like to create a way to ...
Beau Smith's user avatar
6 votes
1 answer
2k views

SQL Change Tracking not tracking multi-record updates

I have SQL Server change tracking switched on a table with about 3mil rows. Switch on change tracking: ALTER DATABASE FooDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF) ...
dylanT's user avatar
  • 221
4 votes
1 answer
3k views

Is it possible to use Change Tracking on a Replication target?

We have a setup where the production SQL Server databases are replicated to an offsite location using SQL Server Replication (transactional replication). I am looking at using SQL Server Change ...
Erick T's user avatar
  • 135
15 votes
2 answers
7k views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
MetaGuru's user avatar
  • 215