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.

By the way, this check is a part of our SQL Server Health Check service.

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

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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