Post reply

Changetracking disabled in database snapshot, new bug in ms sql 2019 ?

  • October 7, 2024 at 10:20 am

    #4465962

    Hi,

    We are using database snapshot together with changetracking to keep a datawarehouse up to date.

    • The database snapshot is used to have transactional consistency for the duration of the warehouse update, without having any locking issues.

      CREATE DATABASE [DB_SS] ON (NAME = 'DB', FILENAME = 'DBFile') AS SNAPSHOT OF [DB]

    • The changetracking functionality is used to insert / update / delete the changed rows, to guarantee a faster incremental update of the warehouse.

      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 :

    • Server 1 : 15.0.4261.1 -> works as expected, changetracking remains active in snapshot copy of the DB
    • Server 2 : 15.0.4365.2 -> broken, changetracking is disabled in snapshot copy of the DB

    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

    #4466180

    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

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply