1.Enable the change tracking at the database level.

ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = ON;

By Default retention is 2 dyas with auto clean up on.We can use below SQL to check it.

SELECT * FROM sys.change_tracking_databases

database_id is_auto_cleanup_on retention_period retention_period_units retention_period_units_desc
----------- ------------------ ---------------- ---------------------- -----------------------------
6 1 2 3 DAYS

SQLServer allow us to sepcify the retention and the auto clean up.
When try to enable the change tracking if it already enabled, then need to disable it first

ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = OFF
GO
ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = ON (AUTO_CLEANUP=ON, CHANGE_RETENTION=1 hours);

SELECT * FROM sys.change_tracking_databases
database_id is_auto_cleanup_on retention_period retention_period_units retention_period_units_desc
----------- ------------------ ---------------- ---------------------- --------------------------------
6 1 1 2 HOURS

Note:
If you got error message saying "Change tracking is enabled for one or more tables in database ‘AdventureWorks2008‘.
Disable change tracking on each table before disabling it for the database.
Use the sys.change_tracking_tables catalog view to obtain a list of tables for which change tracking is enabled."
use below SQL to find out the table and disable it first.refer to section two on how to disable the change tracking on table level.

select object_name(object_id),* from sys.change_tracking_tables

After enable the Change tracking on database level ,then SQLServer create a system internal table sys.syscommittab.
Which we can not query use SELECT except the admin connected in the DAC mode.

SELECT *
FROM sys.all_columns
WHERE object_id = OBJECT_ID(‘sys.syscommittab‘);

object_id name column_id system_type_id user_type_id max_length precision scale collation_name is_nullable is_ansi_padded is_rowguidcol is_identity is_computed is_filestream is_replicated is_non_sql_subscribed is_merge_published is_dts_replicated is_xml_document xml_collection_id default_object_id rule_object_id is_sparse is_column_set
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- ------------ ---------- --------- ----- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- ------------- ----------- ----------- ------------- ------------- --------------------- ------------------ ----------------- --------------- ----------------- ----------------- -------------- --------- -------------
2089058478 commit_ts 1 127 127 8 19 0 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2089058478 xdes_id 2 127 127 8 19 0 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2089058478 commit_lbn 3 127 127 8 19 0 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2089058478 commit_csn 4 127 127 8 19 0 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2089058478 commit_time 5 61 61 8 23 3 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2089058478 dbfragid 6 56 56 4 10 0 NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

--Column Name-- --Type---- -----------Description------------------------------------
commit_ts BIGINT The ascending CSN for the transaction
xdes_id BIGINT The internal identi? er for the transaction
commit_lbn BIGINT The log block number for the transaction
commit_csn BIGINT The instance-wide sequence number for the transaction
commit_time DATETIME The time the transaction was committed
dbfragid INT reserved for future use

List All the change tracking commit history.

select * from sys.dm_tran_commit_table

2.
ALTER TABLE HumanResources.Employee ENABLE CHANGE_TRACKING

ALTER TABLE HumanResources.Employee DISABLE CHANGE_TRACKING
ALTER TABLE HumanResources.Employee ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

list all the tables that already enabled the change tracking feature.

select object_name(object_id),* from sys.change_tracking_tables

For each table, SQLServer will create a system internal table in temp database.

select * from sys.objects where NAME like ‘change_tracking_%‘

3.

DECLARE @context VARBINARY(128) =
CONVERT(VARBINARY(128), SUSER_SNAME());

WITH CHANGE_TRACKING_CONTEXT(@context)
UPDATE AdventureWorks2008.HumanResources.Employee
SET
JobTitle = ‘Production Engineer‘
WHERE
BUSINESSENTITYID=290

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK (
(OBJECT_ID(‘AdventureWorks2008.HumanResources.Employee‘), ‘JobTitle‘, ‘ColumnId‘),
0x0000000006000000);

select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘AdventureWorks2008.HumanResources.Employee‘));

select CHANGE_TRACKING_CURRENT_VERSION()

declare @last_version bigint=1;
select * from changetable(changes AdventureWorks2008.HumanResources.Employee,@last_version)
c left outer join AdventureWorks2008.HumanResources.Employee d on c.businessentityid=d.businessentityid

SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT BusinessEntityID BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
-------------------- --------------------------- -------------------- ----------------------------------------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------- -------------------------------------------------- ---------- ------------- ------ ---------- ------------ ------------- -------------- ----------- ------------------------------------ -----------------------
3 NULL U 0x0000000006000000 0x7000720069006E0063006500730073006400 290 290 134219713 adventure-works\ranjit0 0x95EF 3 Production 88888 1969-10-31 S M 2006-07-01 1 34 37 1 604213F9-DD0F-43B4-BDD2-C96E93D3F4BF 2008-07-31 00:00:00.000

SELECT
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_CONTEXT,
e.*
FROM AdventureWorks2008.HumanResources.Employee e
CROSS APPLY CHANGETABLE
(
VERSION AdventureWorks2008.HumanResources.Employee,
(BusinessEntityId),
(e.BusinessEntityId)
) c where c.SYS_CHANGE_VERSION is not null;

SYS_CHANGE_VERSION SYS_CHANGE_CONTEXT BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------- -------------------------------------------------- ---------- ------------- ------ ---------- ------------ ------------- -------------- ----------- ------------------------------------ -----------------------
3 0x7000720069006E0063006500730073006400 290 134219713 adventure-works\ranjit0 0x95EF 3 Production 88888 1969-10-31 S M 2006-07-01 1 34 37 1 604213F9-DD0F-43B4-BDD2-C96E93D3F4BF 2008-07-31 00:00:00.000

Change Tracking for SQLServer,布布扣,bubuko.com

Change Tracking for SQLServer