October 7, 2024 at 10:20 am
Hi,
We are using database snapshot together with changetracking to keep a datawarehouse up to date.
CREATE DATABASE [DB_SS] ON (NAME = 'DB', FILENAME = 'DBFile') AS SNAPSHOT OF [DB]
ALTER TABLE [dbo].
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);)
Since a few days, we noticed that on one server, the behavior changed, which causes our logic to break.
Normally, when we have changetracking active on a database, then changetracking remains active in any snapshot copy of that database. We need this to read out the incremental changes.
On one of the server, this stopped working, and changetracking is automatically disabled in the snapshot of the database, while it remains active in the original database.
I thought it would a database setting, but cannot find anything relevant.
The only difference I can see, is that both SQL Instances have different versions :
I reviewed all the bugfixes between the 2 versions, and even the bugs fixed after 15.0.4365.2 , but cannot find anything relevant.
Any ideas what could cause the changed behavior ?
Kind regards,
Frederic
3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.
October 7, 2024 at 9:42 pm
Cu would be what I'd think of. I wouldn't be surprised if MS changed something. There are a lot of updates between those versions:
https://www.sqlservercentral.com/articles/sql-server-2019-build-list
October 8, 2024 at 9:19 am
Thanks for your input @steve-2, I checked the release notes of all the CU's before posting this message.
We narrowed down what could cause this behavior, and fix 2962248 (documented in https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate27#2962248) seems to come close, well, at least the proposed workaround seems to work, but it is kind of useless ...
With CU 26 (15.0.4365.2) when we notice that changetracking is disabled in the snapshot copy of the database, we do :
In the new snapshot copy of the production database, changetracking remains active as before.
However, disabling & enabling changetracking on production, causes all the changes to be removed from the changetracking history, and we need to do a full deploy of our warehouse again.
The only workaround I see right now, is reading the changetable from production (instead of the snapshot) and use the snapshot for reading the modified data ...
Thats's a lot of rework, but is do-able ...
3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.
October 8, 2024 at 6:51 pm
That is an unfortunate bug. Have you submitted this on feedback.azure.com and asked others to vote? I don't know why this breaks, but that seems like something they should fix.
October 10, 2024 at 3:43 pm
Hi Steve,
I just reported this as bug on feedback.azure.com
-> https://feedback.azure.com/d365community/idea/ba4b34ea-1d87-ef11-9442-6045bd8115dc
Meanwhile we found a few other workarounds to make it work again.
* change the auto-cleanup from true => false => true
* change the retention period
* disable => enable changetracking
I think some of the internal stuff on change tracking was changed for issue 2962248 ...
3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.
October 10, 2024 at 6:26 pm
Thanks for the update.
Voted as well
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply