Should you use the SQL Server change tracking feature?

What is the SQL Server change trackings feature?

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

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

Should you use Change Tracking?

Change Tracking may save you time from writing custom code, but it’s not an excellent fit for databases with 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 add overhead and may cause potential performance issues.

If you need, make sure you’re only tracking changes on tables that needed it (many tables will bloat internal tables, and this may impact the performance).

If you don’t need it, disable.

How to check if Change Tracking is enabled?

To list all databases that have Change Tracking enabled, use the script below:

  1. SELECT d.name AS 'Database Name', t.*
  2. FROM sys.change_tracking_databases t
  3. 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 using the alter database command with option change_tracking like below:

1.Change [SampleDatabase] to your database name.

  1. ALTER DATABASE SampleDatabase
  2. 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 ‘Change Tracking’ tab:
Figure 1 – SQL Server Change Tracking option.

More information

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

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *