locked
Change Tracking Auto Clean Up SQL Server RRS feed

  • Question

  • How do I know if the Auto Clean Up in Change Tracking in SQL Server worked?

    my Database Change Tracking Properties:

    Change Tracking: True

    Retention Period: 1

    Retention Period Units: Minutes

    Auto Cleanup: True

    I tried querying DML changes for the table using the ChangeTable Function. There are still records returned when I queried using the query below, even the retention period is 1 minute and no DML changes were made.

    SQL Query used:

    select tc.*,Maint_Hrs.* FROM CHANGETABLE(CHANGES [dbo].[Maint_Contract_Hrs_CDC], null) AS Maint_Hrs
    join sys.dm_tran_commit_table tc on Maint_Hrs.sys_change_version = tc.commit_ts

    My question then is, when auto cleanup automatically ran, should I still be seeing these records using the sql query? How do I know if the auto Cleanup worked successfully?

    Sunday, June 28, 2020 3:19 PM

Answers

  • Hi Newbie0001,

    >>How do I know if the auto Cleanup worked successfully?

    Ensure that auto cleanup is working properly using the Extended Event "change_tracking_cleanup". A new extended event, "change_tracking_cleanup", was added to track change tracking automatic cleanup activities. The T-SQL script can be found on our tigertoolbox github repository.

    ChangeTracking auto cleanup is a background thread which wakes up at a fixed frequency (it is invoked automatically every 30 minutes) and purges expired records (records beyond retention period) from the change tracking side tables.

    Please refer to Change Tracking Cleanup to get more details.

    Suggest you also read this  blog about basic knowledge of SQL Server Change Tracking.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, June 29, 2020 1:54 AM

All replies

  • Take a look at this blog post

    https://techcommunity.microsoft.com/t5/sql-server/change-tracking-cleanup-8211-part-1/ba-p/384861

    It explains that automatic cleanup runs every 30 min.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, June 28, 2020 5:46 PM
  • Hi Newbie0001,

    >>How do I know if the auto Cleanup worked successfully?

    Ensure that auto cleanup is working properly using the Extended Event "change_tracking_cleanup". A new extended event, "change_tracking_cleanup", was added to track change tracking automatic cleanup activities. The T-SQL script can be found on our tigertoolbox github repository.

    ChangeTracking auto cleanup is a background thread which wakes up at a fixed frequency (it is invoked automatically every 30 minutes) and purges expired records (records beyond retention period) from the change tracking side tables.

    Please refer to Change Tracking Cleanup to get more details.

    Suggest you also read this  blog about basic knowledge of SQL Server Change Tracking.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, June 29, 2020 1:54 AM
  • Hi Newbie0001,

    Any update? 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, June 30, 2020 7:43 AM
  • Hi Cathy - Thanks for your quick response. I am very new to Change Tracking and a bit confused.

    I tried the script from here but there were no records returned. 

    I also used this query:

    select
    object_name (object_id) as table_name,
    is_track_columns_updated_on,
    min_valid_version,
    begin_version,
    cleanup_version
    from sys.change_tracking_tables

    and the result is from the photo below. 


    I have read the documentation regarding this, but just want to ask to be sure, what does the begin_version mean? Should I be getting a value of zero, to know the auto clean up worked?

    I have not yet used the stored procedure to manually clean up, will keep you posted on this.

    Also, 

    should I still be seeing records based on the query below, if auto clean up worked?

    select tc.*,Maint_Hrs.* FROM CHANGETABLE(CHANGES [dbo].[CDC], null) AS Maint_Hrs
    join sys.dm_tran_commit_table tc on Maint_Hrs.sys_change_version = tc.commit_ts


    • Edited by Newbie0001 Tuesday, June 30, 2020 8:57 AM
    Tuesday, June 30, 2020 8:55 AM
  • Hi Newbie0001,

    >> what does the begin_version mean?

    It means version of the database when change tracking began for the table. This version is usually indicates when change tracking was enabled.  Please refer to Change Tracking Catalog Views - sys.change_tracking_tables.

    I still suggest you using the Extended Event "change_tracking_cleanup" to ensure that auto cleanup is working properly. ChangeTracking auto cleanup is invoked automatically every 30 minutes, suggest you change Change Tracking retention period to two days or other values to have a test again.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, July 1, 2020 7:43 AM
  • Hi Cathy - Thank you so much for your help. Using the Extended Event I can see that the AutoCleanup is running but there were zero rows deleted unfortunately, that's why my side table and the sys.dm_tran_commit_table order are not being deleted. It did finish but no errors were marked so not sure why it is not cleaning up at all. 

    If you have ideas how I can clean up these, please feel free to share. 
    I have already tried manual clean up using the following stored procedure below, but unfortunately it is not working. 

    • sp_flush_CT_internal_table_on_demand
    • sp_flush_commit_table_on_demand

    Saturday, July 18, 2020 4:41 AM