To recap, enabling change tracking at the database level is very simple:
ALTER DATABASE <database name here>
SET CHANGE_TRACKING = <ON | OFF>
(CHANGE_RETENTION = <positive integer> <DAYS | HOURS | MINUTES>, AUTO_CLEANUP = <ON | OFF>);
(Note on the code snippets: I'm using Markdown for this post to simplify my blogging workflow; please let me know if you'd rather I keep using Gists for code samples)
If you don't provide a retention period, SQL Server's default is 2 days. Auto-cleanup defaults to ON unless you tell it otherwise.
The table level commands aren't any more complicated. Before we get started, please note that change tracking requires a primary key on the table you want to track. This is reasonable - you need some kind of unique identifier to tell you which row has changed.
With the PK requirement in mind, on we go!
Much as enabling change tracking at the database level uses a simple
ALTER DATABASE command, enabling change tracking at the table level uses a simple
ALTER TABLE command:
ALTER TABLE Demo_CT
WITH (TRACK_COLUMNS_UPDATED = ON);
There's not a lot going on here either. You have three things to specify:
- The table you want to track changes on
- Enabling or (spoiler alert!) disabling change tracking
- Whether you want to track which column(s) were updated or not
DISABLE and omit the
Speaking of that
WITH, if you don't specify a setting for
TRACK_COLUMNS_UPDATED, SQL Server will default it to
OFF in order to reduce space requirements for change tracking.
While we're talking about
TRACK_COLUMNS_UPDATED, it does just what it says on the tin. This is a good time to remind you that unlike other methods of tracking changes, all this does is tell you that a given record was updated and which columns were updated - it does not give you any state data. We'll cover this in more detail in a future post.
A word of warning: you can't modify the
TRACK_COLUMNS_UPDATED setting without disabling and re-enabling change tracking on the table.
To see which tables have change tracking enabled, query the catalog view
sys.change_tracking_databases, the information is fairly basic. Kendra Little's fantastic article I referenced in my last post has a great script for this, too:
-- Kendra Little made this too; it's also posted at https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/
SELECT sc.name AS tracked_schema_name,
so.name AS tracked_table_name,
ctt.begin_version /*when CT was enabled, or table was truncated */,
ctt.min_valid_version /*syncing applications should only expect data on or after this version */ ,
ctt.cleanup_version /*cleanup may have removed data up to this version */
FROM sys.change_tracking_tables AS ctt
JOIN sys.objects AS so
JOIN sys.schemas AS sc
Of course, feel free to write your own if you need different information!
For our simple example, it gives us:
sys.objects, Kendra's script yet again gives us human-readable results. I can't emphasize enough how important it is to script for readability.
As I mentioned before, disabling change tracking at the table level is equally simple:
ALTER TABLE Demo_CT
Don't forget - if you wish to disable change tracking at the database level, you'll need to disable change tracking for all tables first.
Thank you for continuing to read this series! Please join me next time when I show you around working with change tracking!