Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Change tracking in Sql db & D365 FO

  • 1.  Change tracking in Sql db & D365 FO

    Posted 21 days ago
    Edited by Amer MS 21 days ago
    Is there any use of Sql server 'change tracking' feature in D365 F&O on-premise ? we see some of tables like budget related have 'change tracking' enabled but don't know whether its needed by F&O. For us, its not needed. is this somehow related to entity store ?

    on a separate note, even though retention period is defined as 3 days, the clean up thread seems not working. currently we are running manual stored procedure provided by microsoft to clean up.


    ------------------------------
    Amer MS
    ------------------------------


  • 2.  RE: Change tracking in Sql db & D365 FO

    SILVER CONTRIBUTOR
    Posted 21 days ago
    Here at Dynamics Resources we specialize in Dynamics 365, all versions of AX, and CRM. I ran your question by one of my experts.

    Change tracking enables incremental export of data from Microsoft Dynamics 365 for Finance and Operations by using Data management. In an incremental export, only records that have changed are exported. You can set up this for the "Bring your own database" feature.


    ------------------------------
    Michael Gonzalez
    Account Executive
    Dynamics Resources
    Venon Hills IL
    ------------------------------



  • 3.  RE: Change tracking in Sql db & D365 FO

    SILVER CONTRIBUTOR
    Posted 21 days ago
    Since Data task automation is not currently implemented on-Premises, ChangeTracking is not available.
    For more details, please check the following link:
    https://docs.microsoft.com/en-us/dynamics365/unified-operations/fin-and-ops/get-started/features-not-implemented-on-prem

    ------------------------------
    Michael Gonzalez
    Account Executive
    Dynamics Resources
    Venon Hills IL
    ------------------------------



  • 4.  RE: Change tracking in Sql db & D365 FO

    Posted 20 days ago
    i dig further into this case and found

    1. it is somehow related to data entity used to import data from excel, etc. We have not enabled change_tracking in entities though.
    2. strangely the autoclean thread is not working for one particular table. May be the commit is not happening on it, as rest of tables has reasonable data
    3. the volume of records in change_tracking for this particular budget table is unimaginably huge.
    4. disabling change_tracking from sql mgmt studio is not working as its getting enabled within few moments

    ------------------------------
    Amer MS
    ------------------------------



  • 5.  RE: Change tracking in Sql db & D365 FO

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Change tracking is done via data management.
    It's key in incremental export of information to BYODW, etc.

    In on-premise also it is expected to be same and apart from enabling entities in D365FO, change tracking also needs to be enabled on SQL server side.

    ------------------------------
    Regards
    Rahul Mohta
    Advisor - #D365FO
    Real Dynamics
    California, USA
    ------------------------------



  • 6.  RE: Change tracking in Sql db & D365 FO

    Posted 16 days ago
    ​i am still checking data management, however i find all of them have 'change tracking' disabled.

    ------------------------------
    Amer MS
    ------------------------------



  • 7.  RE: Change tracking in Sql db & D365 FO

    GOLD CONTRIBUTOR
    Posted 16 days ago
    Hi -

    FYI, both MR and Retail use Change Tracking.

    --Here's a query by Brent Ozar that gives you a list of the Tables maintained by Change Tracking

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    GO
    SELECT
       sct1.name AS CT_schema,
       sot1.name AS CT_table,
       ps1.row_count AS CT_rows,
       ps1.reserved_page_count*8./1024. AS CT_reserved_MB,
       sct2.name AS tracked_schema,
       sot2.name AS tracked_name,
       ps2.row_count AS tracked_rows,
       ps2.reserved_page_count*8./1024. AS tracked_base_table_MB,
       change_tracking_min_valid_version(sot2.object_id) AS min_valid_version
    FROM sys.internal_tables it
    JOIN sys.objects sot1 ON it.object_id=sot1.object_id
    JOIN sys.schemas AS sct1 ON sot1.schema_id=sct1.schema_id
    JOIN sys.dm_db_partition_stats ps1 ON it.object_id = ps1. object_id AND ps1.index_id in (0,1)
    LEFT JOIN sys.objects sot2 ON it.parent_object_id=sot2.object_id
    LEFT JOIN sys.schemas AS sct2 ON sot2.schema_id=sct2.schema_id
    LEFT JOIN sys.dm_db_partition_stats ps2 ON sot2.object_id = ps2. object_id AND ps2.index_id in (0,1)
    WHERE it.internal_type IN (209, 210);
    GO

    This is AX2012 R3 CU13 after I enabled Retail.  D365FO has a similar set of tables.
    RETAIL_CHG_TRACKING DESC
    Thanks....Dave




    ------------------------------
    Dave Phillips
    Sr Support Escalation Engineer
    Microsoft
    Fargo ND
    ------------------------------



  • 8.  RE: Change tracking in Sql db & D365 FO

    Posted 7 days ago
      |   view attached
    Further investigation reveals unusual growth in change_tracking records when we upload budget thru data management in on -premise F&O environment. This growth is exponential instead of linear per new record in budget (budgettransactionline table)

    If we upload 9 records, the change tracking is increasing by 45 records, but if we upload 1552 records, the change tracking is increasing by 1.2 million records. Attached is log of record addition i monitored

    This is causing extreme slowness in uploading data and also the auto clean thread is not able to complete in time and thus change_tracking table keep increasing.

    how to understand and fix the exponential growth

    ------------------------------
    Amer MS
    ------------------------------



  • 9.  RE: Change tracking in Sql db & D365 FO

    Posted 7 days ago
    Interestingly i see track_column_update is removed from most of budget tables in PU8.1 but it still there for budgettransactionline

    ------------------------------
    Amer MS
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role