Questions tagged [change-tracking]

Questions about tracking changes to the data in a database.

Filter by
Sorted by
Tagged with
0 votes
0 answers
30 views

Is there any option to have change tracking partition table?

We have one partitioned table which has a lot of changes during week. This are stats for table: table_rows table_size_mb ct_rows ct_size_mb 175 857 125 18 762 2 610 849 197 467 859 Is there any ...
user avatar
  • 307
0 votes
0 answers
99 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 ...
user avatar
  • 1
0 votes
1 answer
102 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 ...
user avatar
  • 45
2 votes
1 answer
59 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 ...
user avatar
  • 1,038
0 votes
1 answer
47 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 ...
user avatar
  • 1,387
0 votes
2 answers
308 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, ...
user avatar
-3 votes
5 answers
231 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 ...
user avatar
1 vote
1 answer
153 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 ...
user avatar
  • 111
1 vote
3 answers
466 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 ...
user avatar
1 vote
1 answer
149 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 ...
user avatar
0 votes
0 answers
200 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].[...
user avatar
  • 101
0 votes
0 answers
115 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 ...
user avatar
7 votes
1 answer
432 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 ...
user avatar
0 votes
0 answers
307 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 [...
user avatar
  • 163
6 votes
2 answers
577 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 ...
user avatar
2 votes
0 answers
251 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, ...
user avatar
1 vote
0 answers
91 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 ...
user avatar
0 votes
1 answer
2k 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 = '...
user avatar
22 votes
1 answer
25k 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 ...
user avatar
  • 1,331
3 votes
0 answers
230 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, ...
user avatar
3 votes
1 answer
748 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 ...
user avatar
4 votes
4 answers
593 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. ...
user avatar
  • 333
1 vote
1 answer
144 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 ...
user avatar
  • 552
0 votes
0 answers
392 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 ...
user avatar
6 votes
2 answers
7k 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; ...
user avatar
0 votes
1 answer
115 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 ...
user avatar
  • 1,370
1 vote
3 answers
75 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 ...
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 ...
user avatar
1 vote
1 answer
101 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 ...
user avatar
3 votes
1 answer
402 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 ...
user avatar
  • 141
2 votes
1 answer
984 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. ...
user avatar
  • 441
3 votes
1 answer
317 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 ...
user avatar
2 votes
1 answer
260 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 ...
user avatar
6 votes
0 answers
613 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 ...
user avatar
  • 161
0 votes
1 answer
589 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 ...
user avatar
  • 101
5 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 ...
user avatar
  • 153
1 vote
0 answers
104 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)? ...
user avatar
0 votes
0 answers
73 views

how to track lob changes?

this question continues this one: I have table that simulate a file system or directory of text files with the following columns: file_name varchar2 not null primary key, file_text clob, ...
user avatar
  • 794
4 votes
1 answer
1k 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 ...
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) ...
user avatar
  • 221
4 votes
1 answer
2k 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 ...
user avatar
  • 135
15 votes
2 answers
6k 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 ...
user avatar
  • 215
1 vote
1 answer
1k views

SQL Server Change Tracking and Replication

My colleagues and I are working on a solution revolving around change tracking… Our goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system. Our database is ...
user avatar
2 votes
1 answer
2k views

Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function

I am trying to create a function wrapping change_tracking_min_valid_version(object_id), to simplify queries when using synonyms instead of table names. Something like this: CREATE FUNCTION ...
user avatar
  • 23
0 votes
1 answer
513 views

Get list of all tables and columns "touched" by application

I need to find out where in the database changes are made, originating from an application. In other words: I'm not interested in the actual value changes, I'm interested in where these changes are ...
user avatar
4 votes
2 answers
1k views

SQL Server - monitor changes on linked server

We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no ...
user avatar
0 votes
3 answers
350 views

SQL Server: track database changes

I have two very similar database questions related to changes tracking. There are a lot of automation tests for an application, all of them use one database backup. Currently before each test ...
user avatar
0 votes
2 answers
6k views

Change Tracking cleanup

Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process? BACKGROUND: I have Change Tracking on database with retention set to 1 ...
user avatar
  • 1,228
7 votes
1 answer
2k 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 ...
user avatar
1 vote
1 answer
6k views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
user avatar
  • 977