Sunday, May 10, 2020

Tracking Data Changes - Change Tracking, Part 3

In my last post, I showed you how to configure change tracking at the table level and how to get configuration information about change tracking from the database engine. We looked at sys.change_tracking_databases and sys.change_tracking_tables, and looked at some sample scripts that present the information in a more readable format.

Before moving on to working with change tracking, I'd like to show you a little bit about how SQL Server handles change tracking data under the hood. Let's take a few minutes to talk about sys.internal_tables, sys.dm_tran_commit_table, and sys.syscommittab. These aren't objects that most DBAs interact with on a routine basis, but they're useful for understanding how change tracking does what it does.

sys.internal_tables is a catalog view that returns one row for each internal table. All objects listed in sys.internal_tables reside in the sys schema of their parent database, and all sport system-generated names that indicate their function. Records here inherit all the usual stuff from sys.objects, plus some useful additional fields for the database engine.

To identify which tables are related with other objects, check the parent_object_id column:



For our purposes, the internal tables we care about are the ones named change_tracking_<parent_object_id>. As you may have surmised, this is the naming convention for the internal change tables.
With some thought you can build out quite a bit of information about the internal tables:



While you can get information about these internal out of sys.internal_tables by joining on some other catalog views, you typically won't be able to get at the contents of the tables themselves:



if you absolutely, positively, cross-your-heart-and-kiss-your-elbow MUST see what's in them, you'll need to use the DAC.

A discussion of the finer points of using the DAC (and yep, you can get at it via Azure Data Studio, and yep, in SQL Server on Linux!) is well outside the scope of this article, so while it's an invaluable tool for emergency use I'm not going to do more than mention it here. Also, the raw contents of the internal change table isn't as useful as you might hope. All that said, I know at least a few of you are itching to see what it looks like, so here you go!



Don't worry! We'll cover the appropriate ways to get at your change data soon.

sys.dm_tran_commit_table is a system dynamic management view that returns transaction information for tables tracked by change tracking. It exposes the information stored in sys.syscommittab, which maps transaction IDs to log sequence numbers. Like the internal change table, sys.syscommittab is cleaned up as part of the change tracking cleanup process, and like the internal change table it can grow quite large if your cleanup interval is too long or if there is a problem with the cleanup process. And, again like the internal change table, you can view its contents using the DAC, and yet again - like the internal change table, the contents aren't terribly useful on their own.



"So you can't easily get the data out of the tables listed in sys.internal_tables and even when you do it's not really helpful; how is this useful, Tim," you ask? From the previous paragraphs, you may have gathered that one important use case for change tracking is finding which internal table stores change data. Once you have the table name you can determine how much space a given table's change tracking information is occupying. Even though change tracking stores minimal information about changes, it can add up. Even if the tracked table doesn't see a lot of INSERT, UPDATE, and/or DELETE operations, if your cleanup interval is too long (or the cleanup task has problems for a long period of time) the table could possibly grow quite large.



I've mentioned cleanup issues a few times. Here too, don't worry! We'll talk about the cleanup soon too.

Thank you for reading this far! In my next post, I'll show you more about working with change tracking. Please join me!

No comments: