Microsoft SQL Server 2012 Bible

Removing Change Tracking

If you find that Change Tracking is causing performance problems or just not accomplishing what you want it to, it's as easy to remove Change Tracking as it is to enable it. All you have to do is disable it from every table, and then remove it from the database.

If the goal is to reduce Change Tracking by a single table, then the same ALTER command that enabled Change Tracking can disable it:

ALTER TABLE HumanResources.Department
 Disable Change_tracking;

When Change Tracking is disabled from a table, all stored ChangeTable data — the PKs and columns updated — are lost.

If the goal is to remove Change Tracking from the database, then Change Tracking must first be removed from every table in the database. One way to accomplish this is to leverage the sp_MSforeachtable stored procedure:

EXEC sp_MSforeachtable
 ‘ALTER TABLE ?
  Disable Change_tracking;';

However, after much testing, in many cases sp_msforeachtable often fails to remove Change Tracking from every table.

A less elegant, but more reliable, method to ensure that Change Tracking is completely removed from every table in the database is to actually cursor through the sys.change_tracking_tables table:

DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + ‘ALTER TABLE ‘ + s.name + ‘.’ + t.name +
 ‘ Disable Change_tracking;'
FROM sys.change_tracking_tables ct
JOIN sys.tables t
 ON ct.object_id= t.object_id
JOIN sys.schemas s
 ON t.schema_id= s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;

Only after Change ...