SQL Server Change Tracking feature

In this page

  • What is Change Tracking?
  • How does Change Tracking work?
  • How to enable and disable Change Tracking in SQL Server
  • Pros of SQL Server Change Tracking
  • Cons of SQL Server Change Tracking

SQL Server Change Tracking is a capability that allows you to track all the changes made to a Microsoft SQL Server database. This feature records all insert, update, or delete operations made to the database schema over a specific time period, and the information received is used for the SQL Server replication process. SQL Change Tracking reads the transaction log to find changes made to the data and then records these changes. This article will briefly discuss how to track SQL Server changes in the database schema using Change Tracking.

What is Change Tracking?

Change Tracking (CT) is a lightweight mechanism for capturing incremental changes happening to the schema during a particular window. You can enable Change Tracking for both databases and tables. However, CT can only be configured for tables that have a primary key. CT won't track individual changes made, but it displays the primary key of the rows for which values changed. All intermediate operations or how many times the row is modified in the table cannot be viewed using CT.

The primary function of CT is efficient data storage and retrieval, which helps in the SQL Server replication process in cases where end-to-end replication fails to work and requires a custom solution. For example, a case where data must be synchronized with data stores that aren't SQL Server databases or have substantially different store formats.

How does Change Tracking work?

In a table with Change Tracking enabled, each row is tracked internally for a change. A version ID is attached for every new insert, update, or delete change in the database.

If Change Tracking is enabled at the database level, each table needs a primary key as its row-level identifier to track data manipulation language (DML) operations. While enabling Change Tracking at the table level, you need to track all the columns to get a clear idea of what has been changed.

Figure depicting the database properties in the Change Tracking window.

For instance, to enable Change Tracking in the database:

ALTER DATABASE Zoho
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)

The three parameters involved here are:

1.CHANGE_RETENTION:

This is the retention period for keeping the Change Tracking information in the database. Records older than X days will be automatically removed if AUTO_CLEANUP is set to ON. The default value is two days.

2.AUTO_CLEANUP:

This removes the tracking information from the database.

3. RETENTION PERIOD UNITS:

This option exists only in SQL Server Management Studio and allows you to set days, hours, or minutes. The minimum retention period value is one minute, and there is no maximum retention period. These parameters are changed after Change Tracking is enabled.

Ensure the database table with Change Tracking enabled has a primary key.

Enabling Change Tracking and tracking the updated columns.
    • Right-click the database in Object Explorer.
    • Select Properties.
    • Select the Change Tracking tab.
    • Set the parameters.
  • Enable Change Tracking for each table you want to audit. The table structure will not be modified.
  • All table row changes invoked by INSERT, DELETE, or UPDATE statements will be tracked and stored. Change Tracking can be enabled across all tables without modifying the actual database schema.

How to enable and disable Change Tracking in SQL Server

Enable Change Tracking:

Change Tracking should be enabled for each table that you want to track. Execute the commands below to enable CT.

At the database level:

ALTER DATABASE database name
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

At the table level:

USE <database name>
GO
ALTER TABLE table name
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

Disable Change Tracking:

Use the sys.change_tracking_tables catalog to view the tables that have Change Tracking enabled. Then, pick the tables or database for which you want to disable CT and execute the commands below.

At the database level:

ALTER DATABASE database name
SET CHANGE_TRACKING = OFF

At the table level:

ALTER TABLE table column name
DISABLE CHANGE_TRACKING;

Pros of SQL Server Change Tracking

In SQL Server auditing, tracking data changes is a crucial process that CT does with ease. SQL Server Change Tracking helps track all the Data Definition Language and Data Manipulation Language changes performed in SQL Server database tables. Analyze the transaction log file changes to identify all the DML operations and commits made on the server.

Cons of SQL Server Change Tracking

It's not possible to use the Change Tracking feature without a primary key in a table. Moreover, Change Tracking is a limited SQL Server database audit solution that tracks only the DML changes that have occurred. Change Tracking identifies the rows that have changed but does not provide information about the values that have been modified. Essentially, CT retrieves only the last change performed on the modified record with no version history of any other intermediate update or delete operations.

Overall, Change Tracking is a useful feature that can effectively monitor changes in a server containing a large volume of data.

What's next?

Securely monitor and track SQL Server data changes with EventLog Analyzer

DownloadGet a product tour