SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete Change Tracking table data sys.syscommittab


Delete Change Tracking table data sys.syscommittab

Author
Message
HBhagat
HBhagat
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3033 Visits: 1075
Comments posted to this topic are about the item Delete Change Tracking table data sys.syscommittab
chris.o.smith
chris.o.smith
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 399
Hi, when I try to run the sql script, I get the following error: "Invalid object name 'sys.syscommittab'". Any advice on how to get this working?
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 14916
chris.o.smith - Friday, February 23, 2018 12:40 PM
Hi, when I try to run the sql script, I get the following error: "Invalid object name 'sys.syscommittab'". Any advice on how to get this working?


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



chris.o.smith
chris.o.smith
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 399
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.

Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 14916
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



chris.o.smith
chris.o.smith
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 399
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!

Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 14916
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!


Thanks for posting back. This is one of those really frustrating things that no one really gets until you hit it. And it really stinks.
But you got a couple of replies from Benjamin. He gave you a different command to try running from the DAC. Please post back after trying that one - I'd really like to see if that works.

Sue



chris.o.smith
chris.o.smith
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 399
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 Smile): 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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search