locked
using sp_flush_CT_internal_table_on_demand did not flush the records RRS feed

  • Question

  • Hi - I just recently installed sql server 2012 sp4 to fix and add sp_flush_CT_internal_table_on_demand.  Currently, my autoclean up with retention period of 5minutes is not cleaning up my side tables and sys_committable table. I've waited a couple of hours and it is still not working.

    Even when I ran manually the stored proc sp_flush_CT_internal_table_on_demand  and  sys.sp_flush_commit_table_on_demand, still it did not flush the records.

    Any ideas how to flush the records?

    exec sys.sp_flush_CT_internal_table_on_demand 'CT_Table'
    exec sys.sp_flush_commit_table_on_demand 1000

    result for sys.sp_flush_CT_internal_table_on_demand:

    Cleanup Watermark = 247171
    Internal Change Tracking table name : change_tracking_168413161
    Total rows deleted: 0.

    Completion time: 2020-07-17T19:53:16.8926321-07:00

    result for sys.sp_flush_CT_internal_table_on_demand:

    (0 rows affected)
    The Change Tracking manual cleanup procedure for database CEI has been executed

    Completion time: 2020-07-17T19:59:56.2205997-07:00

    Saturday, July 18, 2020 3:24 AM

All replies

  • Good day,

    You speak about tasks which are not exactly fit to newbies. You might want to change your user name :-)

    Note: I don't have SQL Server 2012 right now to make the confirmation, but it should probably help you

    1. Please collect the return information from the extended event session with event "change_tracking_cleanup" (at the time that you execute sp_flush_commit_table_on_demand). This should provide more information if the execution of sp_flush_commit_table_on_demand succeed or not and if not then why not.

    2. "my side tables and sys_committable table"

    The name of the table is not "sys_committable" but syscommittab and it is under the schema sys, so the name we use in query is sys.syscommittab

    For example, using DAC you can execute the following query:

    select * from sys.syscommittab
    GO

    Using simple connection you cannot execute this table directly but you can execute the DMV sys.dm_tran_commit_table

    select * from sys.dm_tran_commit_table
    GO

    3. make sure that you have Service Pack 4 for SQL Server 2012 or above when you use sp_flush_CT_internal_table_on_demand SP

    4. sp_flush_CT_internal_table_on_demand cleans up contents from the change tracking side table that's based on the invalid cleanup version. I am guessing that you get zero 

    The value returned by change_tracking_hardened_cleanup_version() is 0.
    The value returned by safe_cleanup_version() is 0.

    5. You can Disable CHANGE_TRACKING on the table and re-enable it. This will clean all the change_tracking_<table object ID> information since the internal side table will be dropped and re-created.

    You can always SET CHANGE_TRACKING = OFF on the database after you disable it on all tables. This will clean all information in the syscommittab table.

    ...

    Try to provide more information if non of this information helped you


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, July 20, 2020 1:27 AM
  • Hi friend,

    The stored procedure sp_flush_CT_internal_table_on_demand enables you to manually clean up the side table (change_tracking_objectid) in a database on which the change tracking is enabled.

    The data that is stored in the sys.syscommittab table and exposed in sys.dm_tran_commit_table management view is subject to cleanup according to the retention period specified when change tracking was configured.

    I tested the Change Tracking Cleanup in my environment. Many times of testing and waiting finally show that the records in the side table and sys.syscommittab table are cleared, but it seems that it cannot be associated with the retention period (few minutes) I set. Including manual cleaning, these records cannot be removed immediately.

    I check this similar post for reference.

    Best Regards,
    Cris


    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, July 20, 2020 7:37 AM
  • Hi,

    Is there any update on this case?
    Please feel free to drop us a note if there is any update.

    Best Regards,
    Cris


    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.

    Friday, July 24, 2020 1:55 AM