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 2 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.
But we can see what columns the table have via query th sys.all_columns catalog.
SELECT object_id, name
FROM sys.all_columns
WHERE object_id = OBJECT_ID('sys.syscommittab');
object_id name
---------------- -------------------
2089058478 commit_ts
2089058478 xdes_id
2089058478 commit_lbn
2089058478 commit_csn
2089058478 commit_time
2089058478 dbfragid
--Column Name-- --Type---- -----------Description------------------------------------
commit_ts BIGINT The ascending CSN for the transaction
xdes_id BIGINT The internal identifi 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. Enable the change tracking on table level.
ALTER TABLE HumanResources.Employee ENABLE CHANGE_TRACKING
SQLServer allow track the update on each column, you need to set option when enable table level tracking.
Disable it first when try to change the option.
ALTER TABLE HumanResources.Employee DISABLE CHANGE_TRACKING
GO
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. Examples on how to use the change tracking feature.
user can set some identifier content on the change contest
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