Questions tagged [change-tracking]
Questions about tracking changes to the data in a database.
71
questions
4
votes
2
answers
451
views
COMMIT_TABLE Waits Crashing System
We run a lot of change tracking in production. Nearly all tables are change tracked. This is for purposes of extracting SQL data to Databricks. Other than the CPU overhead, change tracking rarely ...
- 81
0
votes
2
answers
235
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 ...
- 33
1
vote
0
answers
54
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 ...
- 11
0
votes
1
answer
76
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 ...
- 103
0
votes
1
answer
123
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 ...
- 355
0
votes
1
answer
566
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 ...
- 355
3
votes
1
answer
568
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
...
- 513
2
votes
2
answers
363
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 ...
- 31
3
votes
1
answer
756
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 ...
- 133
0
votes
0
answers
154
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. ...
- 345
1
vote
1
answer
184
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 ...
- 211
0
votes
0
answers
248
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 ...
- 1
0
votes
1
answer
1k
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 ...
- 45
4
votes
1
answer
4k
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 ...
- 1,385
0
votes
1
answer
248
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 ...
- 1,497
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, ...
-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 ...
- 1,523
1
vote
1
answer
526
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 ...
- 111
0
votes
3
answers
2k
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 ...
- 3
1
vote
1
answer
386
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 ...
0
votes
0
answers
421
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].[...
- 101
0
votes
0
answers
273
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 ...
- 307
7
votes
1
answer
1k
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 ...
- 2,029
1
vote
0
answers
544
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 [...
- 173
7
votes
2
answers
1k
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 ...
- 2,029
2
votes
0
answers
422
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, ...
- 447
2
votes
1
answer
540
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 ...
- 447
1
vote
2
answers
6k
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 = '...
- 113
32
votes
4
answers
53k
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 ...
- 1,541
3
votes
0
answers
329
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, ...
- 123
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 ...
- 151
4
votes
4
answers
934
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.
...
- 373
1
vote
1
answer
382
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 ...
- 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 ...
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; ...
- 282
0
votes
1
answer
246
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 ...
- 1,460
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 ...
- 113
1
vote
0
answers
2k
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 ...
- 1,030
1
vote
1
answer
312
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 ...
- 11
3
votes
1
answer
559
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 ...
- 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.
...
- 765
3
votes
1
answer
437
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 ...
- 131
2
votes
1
answer
790
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 ...
- 23
6
votes
0
answers
707
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 ...
- 161
1
vote
1
answer
649
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 ...
- 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 ...
- 163
1
vote
0
answers
161
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)? ...
- 11
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 ...
- 51
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)
...
- 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 ...
- 135