locked
Cannot Find sys.sp_flush_CT_internal_table_on_demand for SQL Server Change Tracking RRS feed

  • Question

  • Hi - I am currently using SQL Server 2019. I am trying to manually clean up records under the  sys.dm_tran_commit_table since the Auto CleanUp did not work even if the Auto Clean Up was set to TRUE and retention period is set to 5 minutes.

    select * from sys.dm_tran_commit_table order by commit_time

    As what I've read from previous blogs, the stored proc for sys.sp_flush_CT_internal_table_on_demand can be used to manually cleanup it, however, I cant find it in my sql server. 

    Where can I find the sp_flush_CT_internal_table_on_demand? Do I have to do something else for this to be enabled?

    exec sys.sp_flush_CT_internal_table_on_demand '[dbo].[Maint_Contract_Hrs_CT]'

    Thank you.

    Thursday, July 9, 2020 1:23 AM

Answers

  • Hi Newbie0001,

    Check the article, this stored procedure applies to SQL Server 2012 SP4.

    Please install the  SQL Server 2012 Service Pack 4 (SP4)(x64) on the server where the SQL Server 2012 is installed.

    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.


    Thursday, July 16, 2020 6:55 AM

All replies

  • Hi Newbie0001,

    Please download the Latest Cumulative Update (CU5) for SQL Server 2019.

    And check following posts if help :
    Change Tracking Cleanup–Part 1 

    https://stackoverflow.com/questions/53524394/manually-cleanup-change-tracking-in-sql-server

    https://dba.stackexchange.com/questions/94141/change-tracking-cleanup

    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.

    Thursday, July 9, 2020 5:57 AM
  • Hi Cris - Thanks for the quick response. I have installed the CU but I am not sure if it has installed correctly. 
    Also, I am sorry if this questions sound dumb, I am very new to SQL Server. I have checked the versions and I think the SSMS is the one that has the 2019 version. This is the version:
    SQL Server Management Studio 15.0.18206.0

    While when I queried the @@Version it returns Microsoft SQL Server 2012 - 11.0.2100.60 (X64). Does this mean the CU that I should be applying is SQL Server 2012 and not SQL Server 2019? I am very confused why I can see different versions.

    Select @@VERSION

    Also, after installation, I tried to check the stored proc but I couldn't find it still. 

    Regards,

    Newbie001

    Saturday, July 11, 2020 12:14 PM
  • Hi Newbie0001

    >>>While when I queried the @@Version it returns Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

    It seems that you are connecting to an instance of SQL Server 2012 instead of an instance of SQL Server 2019.

    Do you connect from SSMS to the local SQL Server, or connect to the remote SQL Server.

    Please make sure the SQL Server instance you want to connect to is SQL Server 2019 version.

    Check : How to tell what SQL Server version you are running

    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 13, 2020 1:27 AM
  • Thank you Cris. Now it's a bit clearer. SSMS is connected to the remote SQL Server.

    Here's the details for
    SQL Server Management Studio 15.0.18206.0
    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  Standard Edition (64-bit) on Windows

    I found the service pack which could be the solution of the missing stored proc for SQL Server 2012:
    https://support.microsoft.com/en-us/help/4018073/sql-server-2012-service-pack-4-release-information

    My follow up questions are:

    1.  Since my SSMS is connected to SQL Server Version 2012, should I install the Service pack where the SQL Server 2012 is installed?

    1. Under the "How to obtain SQL Server 2012 SP4" from the link above, how do I determine which package should I install, as there are 3 packages installed?
    -SQL Server 2012 SP4
    -SQL Server 2012 SP4 Express
    -Microsoft SQL Server 2012 SP4 Feature Pack


    As always, Thank you for taking the time to answer my questions. Appreciate it a lot.


    Thursday, July 16, 2020 6:34 AM
  • Hi Newbie0001,

    Check the article, this stored procedure applies to SQL Server 2012 SP4.

    Please install the  SQL Server 2012 Service Pack 4 (SP4)(x64) on the server where the SQL Server 2012 is installed.

    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.


    Thursday, July 16, 2020 6:55 AM
  • Thank you so much for your help Cris. I was able to see the sys.sp_flush_ct_internal_table_on_demand now. We have successfully installed the sp4. 


    Saturday, July 18, 2020 1:54 AM