Share via

  • Article

Configuring and Managing Change Tracking

This topic describes how to enable, disable, and manage change tracking. It also describes how to configure security, determine the effects on storage and performance when change tracking is used.

Enabling Change Tracking for a Database

Before you can use change tracking, you must enable change tracking at the database level. The following example shows how to enable change tracking by using ALTER DATABASE:

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

You can also enable change tracking in SQL Server Management Studio by using the Database Properties (ChangeTracking Page) dialog box.

You can specify the CHANGE_RETENTION and AUTO_CLEANUP options when you enable change tracking, and you can change the values at any time after change tracking is enabled.

The change retention value specifies the time period for which change tracking information is kept. Change tracking information that is older than this time period is removed periodically. When you are setting this value, you should consider how often applications will synchronize with the tables in the database. The specified retention period must be at least as long as the maximum time period between synchronizations. If an application obtains changes at longer intervals, the results that are returned might be incorrect because some of the change information has probably been removed. To avoid obtaining incorrect results, an application can use the CHANGE_TRACKING_MIN_VALID_VERSION system function to determine whether the interval between synchronizations has been too long.

You can use the AUTO_CLEANUP option to enable or disable the cleanup task that removes old change tracking information. This can be useful when there is a temporary problem that prevents applications from synchronizing and the process for removing change tracking information older than the retention period must be paused until the problem is resolved.

For any database that uses change tracking, be aware of the following:

  • To use change tracking, the database compatibility level must be set to 90 or greater. If a database has a compatibility level of less than 90, you can configure change tracking. However, the CHANGETABLE function, which is used to obtain change tracking information, will return an error.

  • Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, we strongly recommend that snapshot isolation be set to ON for the database. For more information, see Using Change Tracking.

Enabling Change Tracking for a Table

Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

The following example shows how to enable change tracking for a table by using ALTER TABLE:

ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

You can also enable change tracking for a table in SQL Server Management Studio by using the Table Properties (ChangeTracking Page) dialog box.

When the TRACK_COLUMNS_UPDATED option is set to ON, the SQL Server Database Engine stores extra information about which columns were updated to the internal change tracking table. Column tracking can enable an application to synchronize only those columns that were updated. This can improve efficiency and performance. However, because maintaining column tracking information adds some extra storage overhead, this option is set to OFF by default.

Disabling Change Tracking

Change tracking must first be disabled for all change-tracked tables before change tracking can be set to OFF for the database. To determine the tables that have change tracking enabled for a database, use the sys.change_tracking_tables catalog view.

The following example shows how to disable change tracking for a table by using ALTER TABLE:

ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;

When no tables in a database track changes, you can disable change tracking for the database. The following example shows how to disable change tracking for a database by using ALTER DATABASE:

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF

Managing Change Tracking

The following sections list catalog views, permissions, and settings that are relevant for managing change tracking.

Catalog Views

To determine which tables and databases have change tracking enabled, you can use the following catalog views:

Also, the sys.internal_tables catalog view lists the internal tables that are created when change tracking is enabled for a user table.

Security

To access change tracking information by using the change tracking functions, the principal must have the following permissions:

  • SELECT permission on at least the primary key columns on the change-tracked table to the table that is being queried.

  • VIEW CHANGE TRACKING permission on the table for which changes are being obtained. The VIEW CHANGE TRACKING permission is required for the following reasons:

    • Change tracking records include information about rows that have been deleted, specifically the primary key values of the rows that have been deleted. A principal could have been granted SELECT permission for a change tracked table after some sensitive data had been deleted. In this case, you would not want that principal to be able to access that deleted information by using change tracking.

    • Change tracking information can store information about which columns have been changed by update operations. A principal could be denied permission to a column that contains sensitive information. However, because change tracking information is available, a principal can determine that a column value has been updated, but the principal cannot determine the value of the column.

Understanding Change Tracking Overhead

When change tracking is enabled for a table, some administration operations are affected. The following table lists the operations and the effects you should consider.

Operation

When change tracking is enabled

DROP TABLE

All change tracking information for the dropped table is removed.

ALTER TABLE DROP CONSTRAINT

An attempt to drop the PRIMARY KEY constraint will fail. Change tracking must be disabled before a PRIMARY KEY constraint can be dropped.

ALTER TABLE DROP COLUMN

If a column that is being dropped is part of the primary key, dropping the column is not allowed, regardless of change tracking.

If the column that is being dropped is not part of the primary key, dropping the column succeeds. However, the effect on any application that is synchronizing this data should be understood first. If column change tracking is enabled for the table, the dropped column might still be returned as part of the change tracking information. It is the responsibility of the application to handle the dropped column.

ALTER TABLE ADD COLUMN

If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked.

ALTER TABLE ALTER COLUMN

Data type changes of a non-primary key columns are not tracked.

ALTER TABLE SWITCH

Switching a partition fails if one or both of the tables has change tracking enabled.

DROP INDEX, or ALTER INDEX DISABLE

The index that enforces the primary key cannot be dropped or disabled.

TRUNCATE TABLE

Truncating a table can be performed on a table that has change tracking enabled. However, the rows that are deleted by the operation are not tracked, and the minimum valid version is updated. When an application checks its version, the check indicates that the version is too old and a reinitialization is required. This is the same as change tracking being disabled, and then reenabled for the table.

Using change tracking does add some overhead to DML operations because of the change tracking information that is being stored as part of the operation.

Effects on DML

Change tracking has been optimized to minimize the performance overhead on DML operations. The incremental performance overhead that is associated with using change tracking on a table is similar to the overhead incurred when an index is created for a table and needs to be maintained.

For each row that is changed by a DML operation, a row is added to the internal change tracking table. The effect of this relative to the DML operation depends on various factors, such as the following:

  • The number of primary key columns

  • The amount of data that is being changed in the user table row

  • The number of operations that are being performed in a transaction

Snapshot isolation, if used, also has an effect on performance for all DML operations, whether change tracking is enabled or not.

Effects on Storage

Change tracking data is stored in the following types of internal tables:

  • Internal change table

    There is one internal change table for each user table that has change tracking enabled.

  • Internal transaction table

    There is one internal transaction table for the database.

These internal tables affect storage requirements in the following ways:

  • For each change to each row in the user table, a row is added to the internal change table. This row has a small fixed overhead plus a variable overhead equal to the size of the primary key columns. The row can contain optional context information set by an application. And, if column tracking is enabled, each changed column requires 4 bytes in the tracking table.

  • For each committed transaction, a row is added to an internal transaction table.

As with other internal tables, you can determine the space used for the change tracking tables by using the sp_spaceused stored procedure. The names of the internal tables can be obtained by using the sys.internal_tables catalog view, as shown in the following example:

sp_spaceused 'sys.change_tracking_309576141'
sp_spaceused 'sys.syscommittab'