Alright, here's the latest: I tried using the sproc that Ben mentioned (spDiagChangeTracking), but it doesn't exist in my environment. However, I made progress with the original powershell script. In my earlier post, I mentioned that even though the safe cleanup version was equal to the max commit_ts value, nothing was getting deleted. After some digging through the verbose log that the powershell script can produce, I figured out that it was because the hardened clean up version still hadn't incremented (even though the safe cleanup version had). Since I had auto clean-up off, all of these watermarks were out of whack. In my earlier post I also mentioned that I turned auto clean-up back on and set the retention to 2 days - after doing this, it appears that both watermarks EVENTUALLY update and then you can begin deleting records. In my dev tests, the safe clean-up version seems to update about 20-30 minutes after turning auto clean-up back on. The hardened clean up version, however, takes much longer and doesn't seem consistent (at least in my dev environment, which is fairly static but has other developers doing a variety of tasks in it). Sometimes it would take 3 hours before updating, sometimes over 24 hours.
My latest question to Ben is (hopefully this one didn't go to his spam folder :)): do you know of a way to force the hardened clean up version to increment? If so, we could potentially have the following process when we need to clean up syscommittab:
1. Turn auto clean-up on
2. Wait 30 minutes for safe clean up version to update
3. Run process to force hardened clean up version to update
4. Turn auto clean-up off
5. Run powershell SQL agent job
If you have any ideas on #3, please let me know!