Issue with Change Tracking

• alexander.huberti

  SSC-Addicted

  Points: 461

  More actions

  January 16, 2024 at 6:20 pm

  #4362756

  Hi,

  I would like to use change tracking to determine the changes to a table. But it seems that data gets lost. Is this a known problem?

  Microsoft SQL Server 2016 (SP1-CU15) (KB4495257) - 13.0.4574.0

  Setting of change tracking (sys.change_tracking_databases)

  is_auto_cleanup_on: 0

  retention_period: 100

  retention_period_units: 3

  retention_period_units_desc: DAYS

  max_cleanup_version: NULL

  After a while I get far too little data via the query for CHANGETABLE()

  SELECT COUNT(*) FROM CHANGETABLE(CHANGES <TABLE>, 0) AS t

  WHERE sys_change_operation <> N'D'

  returns 421

  SELECT COUNT(*) FROM <TABLE>

  WHERE moddate > CAST('2024-01-16 13:41:21.567' AS DATETIME)

  returns 1507

  The database is part of an AlwaysOn AG

  Transactionlog backup is every hour.

  thanks,

  Alex

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  January 17, 2024 at 7:10 pm

  #4363434

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.