chris.o.smith - Monday, February 26, 2018 9:37 AM

Hi Sue, thanks for all of the detailed info.  We've played around with the settings quite a bit.  Originally, we had our retention period set to 15 days and auto-clean up turned on.  However, in addition to the issue where the CT tables grew faster than the clean up deleted (yes, we are running into the issue that Nic Cain wrote about and had messed around with the internal sproc quite a bit for the last few months but couldn't get it to reliably work), we also experienced significant contention while clean-up was running. So, we tried turning auto clean-up off and cleaning up manually on our own schedule.  This is easy enough for the change tracking side tables, but doesn't address the ever-growing syscommittab table...

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!