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.
Easy!
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
ENABLE CHANGE_TRACKING
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
ENABLE
to DISABLE
and omit the WITH
clause.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_tables
:As with
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.is_track_columns_updated_on,
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
ON ctt.[object_id]=so.[object_id]
JOIN sys.schemas AS sc
ON so.schema_id=sc.schema_id;
Of course, feel free to write your own if you need different information!
For our simple example, it gives us:
Joining on
sys.schemas
and 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
DISABLE CHANGE_TRACKING;
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!
3 comments:
Wynn casino opens in Las Vegas - FilmfileEurope
Wynn's first hotel casino nba매니아 in Las Vegas since opening its doors in 1996, Wynn Las Vegas is the first hotel on the Strip to https://jancasino.com/review/merit-casino/ offer such 출장안마 a large selection of worrione
Thankks for a great read
Nice blog, thanks for posting
Post a Comment