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:
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 using the alter database command with option change_tracking like below:
1.Change [SampleDatabase] to your database name.
ALTER DATABASE SampleDatabase
SET CHANGE_TRACKING = OFF
Also, you can use SQL Server Management Studio:
- In Object Explorer right click on the database
- Click ‘Properties’ and select false on ‘Change Tracking’ tab:
More information
Microsoft – About Change Tracking
Microsoft – Enable and Disable Change Tracking
