This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Learn More

Remove From My Forums

sys.sp_flush_commit_table_on_demand did not delete/flush all rows under sys.dm_tran_commit_table for SQL Server Change Tracking

Question
0

Sign in to vote
Hi - Currently, these are the records which are present in sys.dm_tran_commit_table. Please know my retention period is 5 minutes and Auto CleanUp is set to False as I want to manually run the auto cleanup, because it is not Auto cleaning up accordingly.

select * from sys.dm_tran_commit_table order by commit_time

I am using SQL Server 2019.

When I ran the stored proc sys.sp_flush_commit_table_on_demand, it did not delete/flush all the rows under the sys.dm_tran_commit_table and keeps returning 0 rows deleted. Do I need to do something prior to executing the stored procedure? I've ran it a couple of times repeatedly but still wont delete.

Please advise.

EXEC sys.sp_flush_commit_table_on_demand 1000

Thank you.
Thursday, July 9, 2020 1:37 AM

All replies

0

Sign in to vote

This issue is fixed in the following cumulative updates for SQL Server:

Cumulative Update 4 for SQL Server 2019

Reference here:fix-change-tracking-cleanup-does-not-work-when-invalid-cleanup-and-har

Friday, July 10, 2020 2:43 AM
0

Sign in to vote
Hi MIAOYUXI, thanks for the response. I did try to install the Cumulative Update. However I am not sure if it has installed correctly. I know this might sound a dumb question, how do i know if the CU has been installed properly?

I also tried to rerun again the stored proc, but still doesn't work unfortunately.

Thank you,

Newbie0001
- Edited by Newbie0001 Saturday, July 11, 2020 10:09 AM
Saturday, July 11, 2020 10:09 AM
0

Sign in to vote

Actually,you can isntall the latest CU, because each new CU contains all the fixes that were included with the previous CU for the installed version of SQL Server.

After installed,you can check as next:

select @@version

Monday, July 13, 2020 1:35 AM
0

Sign in to vote

Hi MIAOYUXI, thanks for your help. Ive checked the version and what I have is SQL Server 2012. I have already installed the latest Service Pack which is SP4. I ran the stored proc again but still it is not flushing records (sys.sp_flush_commit_table_on_demand).

I also got a new stored proc (sys.sp_flush_CT_internal_table_on_demand). I executed this manually but still it is not flushing the records.

Saturday, July 18, 2020 4:07 AM

Sign in to vote

Sign in to vote

You need to disable auto-cleanup for a time period.

Reference: performance-tuning-sql-server-change-tracking, sql-server-change-tracking-performance-troubleshooting

,adds-a-stored-procedure-for-the-manual-cleanup-of-the-change-tracking

Thursday, July 23, 2020 8:54 AM

0

Sign in to vote

Hi,

I'm following this open case and is the issue solved?

You can choose to mark the helpful reply as answer to close this case.

BR,

MIAOYUXI

Friday, July 24, 2020 9:31 AM