Category Archives: Uncategorized

Change Tracking – what is really going on during change tracking cleanup operations?


In  my environment we see an occasional situation where a system process blocks user process.  To find what the system process was doing we looked to the wait_resource column in sys.dm_exec_requests to see what pages the user session was waiting  on. We then took the pages numbers and looked to DBCC PAGE  to determine the object. This led us to the syscommittab table – the global ‘log’ table for the change tracking feature. Of course we noticed all the blocked user process use change tracking functions so that was a clue as well…

I set out to find out what I could about the cleanup process. I’m not the first one to have problems with change tracking. Here are a couple links where others are trying to get a handle on this –  mostly – black box:

These posts gave me a better understanding –  especially with regards to how the side tables and the syscommittab log table hand off “delete-watermark” values to each other (i.e. versions less than a “delete-watermark” will be deleted):

Using a test database with an approx 20 million row syscommittab I was able to watch the process via extended events and came up with these observations.

  • Auto-cleanup runs every 30 minutes. The initial cleanup seems to be run is 30 minutes after auto-cleanup is first turned on. Successive runs are every 30 min after the end of the previous cleanup. This means long running cleanup operations will postpone any further cleanup.
  • Auto-cleanup process side tables (in descending order by side-table object id) serially. Side table rows are deleted in batches of 5000. Some cleanup process – especially for large tables – do not complete and that failure is logged in SQL Error Log as ‘ <side table blocked> ’ type messages.
  • Only after all side tables have been processed (with or without the ‘side table blocked’ error) does the auto-cleanup go to work on purging syscommittab. The syscommittab cleanup works in batches of (default) 10000. The syscommittab cleanup process takes the minimum cleanup version from all the recent side-table cleanups and deletes rows from syscommittab with versions older than that minimum cleanup version. If the minimum cleanup version returned by the side table cleanup is less than the retention version (per change traking configuration retention setting) no syscommittab rows can be cleaned up. It can be a long time between syscommittab cleanups due to ‘every 30 min’ (bullet 1 above) and ‘side table blocked’ error (bullet 3 above) which inhibits the advancement of the minimum cleanup version.
  • Manual side table cleanup follows the same rules as auto-cleanup – namely it will not clean any rows unless auto-cleanup is turned on and then the first cleanup cycle starts after auto-cleanup has been tuned on for 30 minutes.
  • Manual syscommittab cleanup also requires auto-cleanup to be turned on. If the most recent minimum cleanup version returned by the tables is less than the retention version no syscommitab rows can safely be cleaned up and no rows will be deleted.

Due to all the points above you can see the cleanup process is less than ideal and syscommittab may not be cleaned often enough to keep up with growth. And the larger it gets the longer between cleanup cycles which leads to further un-checked growth. When (if) the cleanup does finally kick in on syscommittab, the delete process can be long-running – up to 40 minutes in test cases. During this syscommittab delete process the locks against the syscommittab table block user session using change tracking functions.

Also of interest, once a ‘delayed’ delete process does complete on a large syscommittab table there is typically a long checkpoint (5 min in tests) which has production impacts of it’s own…


Stay tuned for more on change tracking


Au Revoir 2016, Bienvenue 2017!

Au Revoir 2016, Bienvenue 2017!

Ca Va! No, I don’t speak French but that never stopped these guys: Foux Da Fa Fa. And this title, as they say in France, has a certain “oh I don’t know what” ring to it.

Sacrebleu I was a busy throughout 2016. Mostly working on… well… mostly the work this blog documents, but also unclogging garbage disposals, driving kids to bus stops, fixing broken things, and filling\emptying wheelbarrows. It seems I barley took the time to look up and look around. Looking back on it now with a bit of the ol’  l’esprit d’escalier, I can see it was clearly an amazing year for me! Here are a few highlights in photos:

Le Grand Teton

In June my friend Dan and I climbed to the summit of the Grand Teton. We had tried this climb quiet a few times in past years, so it was excellent to finally achieve it. I love it there and I want to go back.

tetonsfromgate gtsummitme

Le Labyrinthe

The Maze is one of three districts in Canyon Lands National Park. It has so much great stuff: natural and cultural history, rivers, rocks, and sky. My friend Greg and I loaded up our bikes rode in the 20-or-so miles to set up a base camp for a few days of exploring. I love it there and I want to go back.

memazebygreg gregandpackedbikes


Back to the professional side of things, I had the chance this year to present at the Professional Association of SQL Server. I partnered up with Pinal Dave to deliver a talk on Database Backups in SQL Azure VMs. Pinal is a great guy and I really had fun hanging out with him while we prepped. (Our topic and presentation slowly gelled as we took every opportunity to meet, both in Bangalore IND as well as Seattle USA). The 10am presentation itself went well with Pinal’s exuberance and humor saving us all from falling back to sleep. We ended up with a deadpan\comic dynamic as you might be able to see in the photo. Not sure I’ll ever get the chance to do that again, but if I do I’ll take it.


India 2016

I visited Mumbai and Bangalore India for my second time (last time was 2014). This trip I gave a few classes on Azure to DBA colleagues but just as importantly I had the chance to hang out with awesome people in a really cool country. I love it there and I want to go back.


So, to summarize…


2. OK 2017, LETS GO!

‘Till next time, enjoy every baguette

I’ve got a blank space baby… and it is filled in by my ConfigurationFile.ini

Check this out – the almost last page of a SQL install, Ready to Install. See the configuration file path at the bottom?


Recently I have been doing many SQL installs on Azure VMs. As AOAG ‘nodes’, each install needs to be, basically, the same. There are options for imaging Azure VMs but in this case I don’t have access to the portal. As a time saver I have been using these configuration files.

As I complete the first install with everything set up as required, I copy the configuration file path and then kick off the install. I paste that path into Windows Explorer which opens the .ini file — select all and copy. Next I move to my secondary, or secondaries, and quickly create a new text file on the desktop, paste, and save.

When I fire up the install-able on the secondary, I don’t go to ‘Installation’ as usual, instead I go to ‘Advanced’ and choose ‘Install based on a configuration file’.


Now I have the chance to browse to the .ini config file on the desktop (kinda PITA that it has to be an .ini).


Once that loads I’m off and clicking! Next, next, next, next, next, next, next with Taylor Swift tunes  echoing through the catacombs of my mind. Where necessary, I still need to input passwords, but otherwise all the “blank spaces” are filled in;  feature selections, root directory, shared feature directory, server and engine configuration (e.g. data directories), and so on.

No big deal, but a bit of a time saver when I want a set of SQL Servers to ‘match’ and it helps me to avoid one-off mis-configurations.