SQL Server Change Tracking: Benefits, Limitations, and When to Use

What is the SQL Server change tracking feature?

Change tracking was introduced in SQL Server 2008 to help you sync data.

It enables relatively easy access to new, changed, and deleted data, eliminating the need for brute-force comparisons or other costly methods of change detection.

Should you use change tracking?

Change tracking can save you time by eliminating the need for custom code.

However, it is not an ideal solution for databases experiencing a very high rate of transaction commits to the tracked tables.

The feature is commonly used in data warehousing applications where you need to pull incremental data (changes only) from your source.

Also, it can be used as a foundation for both one-way and two-way synchronization applications.

In these kinds of scenarios, change tracking is very beneficial.

However, it adds overhead and may cause potential performance issues.

If necessary, ensure you're only tracking changes on tables that require it, as tracking many tables can bloat internal tables and impact performance.

If you don't need it, disable it.

How to check if change tracking is enabled?

To list all databases that have change tracking enabled, use the script below:

SELECT d.name AS 'Database Name'
	,t.*
FROM sys.change_tracking_databases t
INNER JOIN sys.databases d ON d.database_id = t.database_id

How can I disable the change tracking on a SQL Server database?

You can disable it using the ALTER DATABASE command with the option CHANGE_TRACKING like below.

Don't forget to change [SampleDatabase] to your database name.

ALTER DATABASE SampleDatabase

SET CHANGE_TRACKING = OFF

Also, you can use SQL Server Management Studio:

1. In Object Explorer, right-click on the Database
2. Click Properties and select False on the Change Tracking tab:

More information:

• Microsoft – About Change Tracking
• Microsoft – Enable and Disable Change Tracking