How to Enable Change Tracking – SQL [Complete Guide 2024]

Businesses perform several tasks like auditing data records, checking malicious activities, tracking security violations, and ensuring seamless business operations. They often use SQL servers to track database activities and audit the changes in their data. With this, all the database operations, like insertion, deletion, and updation, are captured periodically. 

The change tracking feature in SQL is one such feature that enables companies to capture the changing rows and columns in a database. Change tracking in SQL is a simple mechanism that can capture incremental changes in a specified window. This article will introduce you to the change tracking mechanism in SQL servers and how to enable and disable it.

What is Change Tracking?

Change tracking refers to the process of recording and monitoring changes made to a document, system, or dataset over time. It is a useful feature in many software applications and platforms, particularly in collaboration, content management, and version control systems. The main purpose of change tracking is to provide a transparent history of modifications, making it easier to review, approve, or revert changes if necessary. 

How to Enable Change Tracking for a Database?

SQL Servers provide two features to track changes to data in a database. The first is change data capture, and the second is change tracking. Change data capture is the method of identifying and storing the changes that happen in databases. While change tracking is the process where database changes are identified, it does not determine the data that is changed. Both change data capture and change tracking can enable DML changes and track which rows and columns were changed. But, only change data capture can determine the exact changes in databases.

The ability to query for changes in a database is an essential requirement for some database applications. To determine the data changes, application developers implement custom tracking event methods like triggers, timestamps, and additional columns in applications. But deploying these methods requires custom implementation, which leads to schema updates and causes high-performance overhead. 

As a result, businesses use change tracking or data capture mechanisms for databases to track changes instead of developing a custom solution.

To use change tracking in the SQL server, you must first enable change tracking at the database level. After enabling the change tracking at the database level, you can enable change tracking for each table in the database. 

The example below shows how to allow change tracking using the ALTER DATABASE.

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

• CHANGE_TRACKING: It is the parameter used to enable/disable change tracking in the SQL server.
• CHANGE_RETENTION: It is the option used to specify the time for which change tracking information is kept in the database. The default value for CHANGE_RETENTION is 2 days.
• AUTO_CLEANUP: It is the option used to remove all the old change tracking information. 

Businesses can also enable change tracking with SQL Server Management Studio by leveraging the dialog box of the database properties (ChangeTracking page). 

How to Enable Change Tracking for a Table?

Change tracking needs to be enabled for every table you want to track. Then, the change tracking information is collected for all rows in the tables which are affected by DML operations.

The example below shows how to enable change tracking for a table using ALTER TABLE.

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

TRACK_COLUMNS_UPDATED: It is the option used by the SQL server database engine to store extra information about the columns. This information helps in improving efficiency and performance. But maintaining column information adds to some extra storage overhead, and hence the TRACK_COLUMNS_UPDATED is set to OFF by default.

You can also enable change tracking for tables in SQL server management studio by leveraging the Database Properties dialog box.

How to Check the Change Tracking Status in SQL Server?

1. Enable change tracking at the database level in the SQL server

Check if you’ve enabled change tracking at the database level with the following syntax:

SELECT *
FROM sys.change_tracking_databases
WHERE database_id = DB_ID(‘databasename’);

For example:

SELECT *
FROM sys.change_tracking_databases
WHERE database_id = DB_ID(‘Adventureworks2019’);

Output:

The SQL server will show the above output after enabling the change tracking mechanism on a database level. 

The database with database_id as 8 has been enabled for change tracking. 

2. Enable change tracking at the table level in the SQL server

Check if you’ve enabled change tracking at the table level with the following syntax:

USE databasename;
SELECT sys.schemas.name as schema_name, sys.tables.name as table_name
FROM sys.change_tracking_tables
JOIN sys.tables ON sys.tables.object_id = sys.change_tracking_tables.object_id
JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id;

For example:

USE Adventureworks2019;
SELECT sys.schemas.name as schema_name, sys.tables.name as table_name
FROM sys.change_tracking_tables
JOIN sys.tables ON sys.tables.object_id = sys.change_tracking_tables.object_id
JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id;

Output:

DEMO_SECOND is the table for which the change tracking has been enabled.

How to Disable Change Tracking for a Database or a Table?

When there are no tables in the database to track changes, disable the change tracking for the database. The below example shows how to disable change tracking for a database with the ALTER DATABASE in the SQL server.

ALTER DATABASE AdventureWorks2012  
SET CHANGE_TRACKING = OFF

Here, CHANGE_TRACKING: is the parameter used to enable or disable change tracking in the SQL server.

The below example shows how to disable change tracking for a table via ALTER TABLE in the SQL server.

ALTER TABLE Person.Contact  
DISABLE CHANGE_TRACKING;

Learn More About:

Oracle Change Tracking

Common Issues Faced while Implementing Change Tracking

1. Performance Overhead: Constantly tracking changes in real time can strain system resources, especially in large documents, databases, or systems with frequent modifications.
2. Conflicts in Collaboration: In multi-user environments, when multiple people work on the same document or data, conflicts can arise if two or more users modify the same section.
3. Difficulty in handling large datasets: In complex systems like software codebases or large databases, tracking granular changes across many files or records can become cumbersome.
4. Version Bloats:  With frequent edits and saved versions, the change history can grow large, leading to “version bloat” in both documents and systems.

Best Practices for Optimizing Change Tracking Performance

1. Only track essential changes. Avoid tracking unnecessary data (such as minor formatting changes) or unimportant areas of a document or system.
2. Instead of tracking each modification in real-time, implement a batching system that records changes at set intervals (e.g., every few seconds or after a user finishes editing).
3. In database environments, ensure that the queries used to track, retrieve, and display changes are optimized. Use indexing to speed up lookups and data retrieval.
4. Decouple the change tracking process from the main user operations by handling it asynchronously. This means changes are tracked in the background without interrupting user actions.

How Hevo Supports Change Tracking?

Hevo supports Change Tracking for SQL Server. Hevo uses Change Tracking (CT) instead of Change Data Capture (CDC) for SQL Server as CT requires lesser data to be managed and stored at the Source. Change Tracking replicates the historical data changes and Hevo pushes these changes to the Destination in near real-time, compared to CDC which maintains all the historical changes to the data at the Source.

To implement Change Tracking in SQL Server using Hevo, follow these steps:

Step 1: Select SQL Server as your Source.

Step 2: Select “Change Tracking” as your replication mode.

Step 3: Configure your destination. 

Summing Up

Change tracking in SQL servers helps track changes in databases. Since many people handle databases, change tracking is essential for database administrators to track all the modifications. Change tracking is a lightweight solution supported by all versions of SQL servers. You must use the feature to ensure you keep track of the changes in mission-critical applications.

Getting data from many sources into destinations can be a time-consuming and resource-intensive task. Instead of spending months developing and maintaining such data integrations, you can enjoy a smooth ride with Hevo Data’s 150+ plug-and-play integrations (including 60+ free sources).

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions