CODEWITHSUNDEEP
sql-serverchange-tracking
brewsky
brewsky

Reputation: 617

Manually cleanup CHANGE TRACKING in SQL Server

I am trying to manually clean the change tracking tables within SQL Server 2017.

I run the command 

exec sp_flush_CT_internal_table_on_demand 'mydb.data.foobar'

Error message:

Msg 2501, Level 16, State 1, Procedure sys.sp_MSflush_CT_internal_table_on_demand, Line 22 [Batch Start Line 40]
Cannot find a table or object with the name "mydb.data.foobar". Check the system catalog.

The table 'mydb.data.foobar' does exist.

The following successfully returns: 

select object_id('mydb.data.foobar')

Anyone ever manually clean CT ?

Upvotes: 3

Views: 6327

Answers (2)

Brain2000
Brain2000

Reputation: 4894

The question here is several years old, but I stumbled upon a solution today, as I've also had this issue for years.

For anyone using Change Tracking combined with Logging set to something other than Simple, refer to KB4500403 and make sure you have installed the latest cumulative update.

This article refers to large transaction logs, but it also addresses issues with Change Tracking Cleanup not working in general. Until I installed this cumulative update, not only was I not able to run Change Tracking Cleanup, but I also had to disable Automatic Cleanup because it was holding a CPU core at 100% all day long and generating 50GB+ logfiles for highly active databases.

Here's a reference for the issue: https://support.microsoft.com/en-us/topic/kb4500403-fix-tlog-grows-quickly-when-you-run-auto-cleanup-procedure-in-sql-server-2014-2016-and-2017-41a5a303-b0b3-e9d8-a540-597ad27b584b

Upvotes: 0

CR241
CR241

Reputation: 2613

There is a system stored procedure available sys.sp_flush_commit_table_on_demand in case we want to do a manual cleanup with configurable batch size. However it should be used only in cases where you can not manage the Change Tracking internal tables with auto cleanup.

You should also disable Auto Cleanup whenever you run manual cleanup otherwise they may block each other.

EXEC sp_flush_commit_table_on_demand 100000

Use something like this:

EXEC sys.sp_flush_CT_internal_table_on_demand 'Table_Name'

It work for me. I think you missed sys.

Setting auto_cleanup = false basically means cleanup is disabled, this allows you to do any troubleshooting if you're having sync issues that need debugging/investigation.

If this isn't helpful, check this: Change tracking cleanup process in SQL Server 2016 and 2017

Upvotes: 3

Related Questions