I hadn't seen the the Benjamin Reynolds post before and got super excited after reading through it - that seemed like a perfect solution. However, I'm running into the same quirks as I did when I first learned about the sp_flush_commit_table_on_demand sproc - nothing seems to get deleted even when it appears that it should work. For example, here's what I tried to do in our dev environment: we have a database with CT turned on and it currently has 148,962,939 records in the syscommittab table. The most recent commit_time in the table is 2018-02-14 14:17:16.333 (with a commit_ts of 294788369), and the oldest commit_time is 2017-03-28 23:17:06.740 (with a commit_ts of 156972007). At first, I had auto clean-up turned off (because, as I mentioned earlier, we turned it off due to contention issues). However, the "Safe Cleanup Version" won't ever change if auto clean-up is off. So, I turned it back on with a retention period of 2 days. I left the database alone over the weekend and then tried to run Benjamin's powershell script this morning. The results from the log are as follows:
26-02-2018 08:12:04.12 Script Starting
26-02-2018 08:12:05.12 Current Total Syscommittab Records = 148962939
26-02-2018 08:12:05.12 No ScopeId returned so we can't continue to try and log to a table any more!
26-02-2018 08:12:14.12 Script Completed!
26-02-2018 08:12:14.12 Last Values:
26-02-2018 08:12:14.12 Safe Cleanup Version: 294788369
26-02-2018 08:12:14.12 Syscommittab Rows deleted: 0
26-02-2018 08:12:14.12 Deadlocks Encountered: 0
26-02-2018 08:12:14.12 Number of Iterations: 9
26-02-2018 08:12:14.12 Syscommittab Records Start: 148962939
26-02-2018 08:12:14.12 Syscommittab Records End: 148962939
You can see that the "safe cleanup version" has appropriately updated to the max commit_ts in the table, since it is older than 2 days. However, the sp_flush_commit_table_on_demand sproc still didn't delete anything, and I have no idea why... I submitted a question about this on Benjamin's blog, so hopefully I can get a little guidance from there - if so, I'll come back to this post and update with any progress I make. If you have any suggestions, please let me know! Thanks again for your time!