• chris.o.smith - Friday, February 23, 2018 3:47 PM

    Sue_H - Friday, February 23, 2018 2:41 PM

    Did you try to run it using sqlcmd or did you try to run it in SSMS?
    You need to use the DAC to query sys.syscommittab. That's what the -A option is for in the sqlcmd used to run the script.

    Sue

    Thanks, that did the trick for me.  In our current set-up, this table is growing faster than the built-in clean-up process deletes from it.  As a result, syscommittab is slowly growing, and it's starting to impact CHANGETABLE query performance.  We're looking at the possibility of using the method described in this post to do an occasional clean-up of the table, until we upgrade to SQL Server 2017 (there were some improvements to the CT clean up process in SS2017, outlined here, that I think will solve our problems).  Outside of change tracking, are you aware of any gotchyas or concerns when manually deleting from sys.syscommittab in this way?  Online documentation indicates that the table is used for other purposes outside of change tracking.

    I wouldn't. If you were to go down the road of using undocumented methods to clean it up, I would look at the undocumented stored procedure instead as it has some additional internal functions to check what is safe to delete.
    But first I would really look at the settings - mostly retention period and making sure the auto cleanup is enabled.
    You can have a situation where the cleanup can't keep up with the number of transactions. There is an article about it and a query to see if you are hitting this -
     Change Tracking Cleanup Limitation

    That same guy has written about the undocumented stored procedure to clean up - sys.sp_flush_commit_table_on_demand. There are some other things written about that so I would search on that and read all you can. The article I first read on it (and the link above) is on the SirSQL blog -
    Change Tracking Cleanup - Going Beyond Automatic

    Edit - added: Here is the other blog post I was looking for where Microsoft mentions the same stored procedure and why you would want to use that, how the table is used by replication as well, etc. And how it doesn't always work...
    Change Tracking (aka Syscommittab) Issues and Cleanup

    The post also lists some other things to try as well. I'd look at all of those things instead of just deleting everything from that table.

    Sue