December 11, 2025 at 11:32 am
I have change tracking configured in several databases, in QA and production environments, and we have config in place to feed from these databases to data warehouse databases. It works well, but ...
In a QA environment, we have been receiving the following sev-16 alert for a few days:
Change tracking autocleanup is currently not able to maintain retention for database ID n. Number of expired records: 1928. If this warning persists, check the following resource: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/cleanup-and-troubleshoot-change-tracking-sql-server
I have checked the suggested 'resource', but cannot seem to get to the bottom of this. Autocleanup appears to be running without issue.
Can anyone explain exactly what is meant by an 'expired record' in this context and perhaps suggest some steps I can take to identify or resolve this issue?
Thanks for any suggestions!
December 11, 2025 at 3:41 pm
This apply?
Expired records should be those that are in the tables, but should be deleted. Are you saying based on settings there aren't any records in the on-disk table that shouldn't be there?
Same instance version/CU/db version across all?
December 15, 2025 at 5:20 pm
Hi Steve, sorry for the slow reply.
One of the database sources is SQL Server 2022, CU22, with DB at compatibility level 100.
Another is an Azure SQL database which is running at comptibility level 160.
The target DB SQL Server 2022 CU22, at compatibility level 160. The alert is generated on the target server.
How can I identify the expired records? Or if I can at least identify which tables contain them, I can simply do a full wipe/refresh of those tables.
December 16, 2025 at 4:50 pm
Not entirely sure, but I do know there is a proc that you can run, which might do what the auto cleanup is missing: sp_flush_CT_internal_table_on_demand
I might run that and capture the output var to see if there is something returned. I didn't see that proc in the article you linked above. Doesn't seem that there are enough tools for CT.
December 19, 2025 at 9:49 am
Thank you for the response, Steve.
I have tracked down the problem – for testing purposes, a database restore had been performed, from a database with CT enabled. Nothing was consuming the CT history for the changes occurring in this restored database and I guess that caused the expiration warning. I have now disabled CT on that database.
December 19, 2025 at 11:04 am
Test ( disregard )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2025 at 6:13 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply