How to disable change tracking on the database, tables

Problem: How to disable change tracking.

Solution: 

SET CHANGE_TRACKING = OFF

DISABLE CHANGE_TRACKING 

sys.change_tracking_databases – change tracking information of databases

sys.change_tracking_tables – change tracking information of tables.

These two views store change tracking information on enabled databases, enabled tables.

To Disable the change tracking on a database first we have disable change tracking on all the tables that having change tracking enabled on that particular database and then disable change tracking database.

To know more about what is change tracking and how to enable change tracking please go through this article: Enable Change Tracking

Example:

In this article I am going to show you we can disable change tracking on tables, database.

Example 1: 

In this example, I would like to show how we can obtain if change tracking tables enabled on a table and then disable the change tracking on that table.

IF EXISTS (select 1 from sys.change_tracking_tables where object_name(object_id)='Orders')

BEGIN

ALTER TABLE dbo.orders DISABLE CHANGE_TRACKING 

END

Example 2: 

In this example, I would like to show how we can obtain all the changed databases which are enabled and disable the change tracking on the database. To disable the change tracking first we have to check that no table has change tracking enabled and then turn of the change tracking on the database,

IF NOT EXISTS (select 1 from sys.change_tracking_tables)

BEGIN

IF EXISTS (select 1 from sys.change_tracking_databases where db_name(database_id)='db')

BEGIN

                ALTER DATABASE DB  SET CHANGE_TRACKING = OFF 

END

END

Feature Script:

This script checks the change tracking of the tables on the table and loops through to execute the disable change tracking on all the tables.

DECLARE @EXECSQL VARCHAR(4000),

@msg VARCHAR(4000)

, @TEMP NVARCHAR(1024)

,@DB VARCHAR(512) ='DB'

IF OBJECT_ID('tempdb..#Changetemp') IS NOT NULL

                DROP TABLE #Changetemp

CREATE TABLE #Changetemp (SQLCommand VARCHAR(2000))

SELECT @ExecSQL = 'USE ' + @DB + ' SELECT ''ALTER Table ['' + S.name + ''].['' + OBJECT_NAME(T.object_id) + '']  DISABLE CHANGE_TRACKING''

                from sys.change_tracking_tables T

                inner join sys.tables TT on TT.object_id = T.object_id

                inner join sys.schemas S on S.schema_id = TT.schema_id '

INSERT INTO #Changetemp (SQLCommand)

EXEC (@ExecSQL)

IF EXISTS (

                                SELECT SQLCommand

                                FROM #Changetemp

                                )

BEGIN

                SET @ExecSQL = ''

                DECLARE ChangeTrackingTables CURSOR

                FOR

                SELECT SQLCommand

                FROM #Changetemp

                OPEN ChangeTrackingTables

                FETCH NEXT

                FROM ChangeTrackingTables

                INTO @Temp

                WHILE @@Fetch_Status = 0

                BEGIN

                                --PRINT @TEMP

                                SET @ExecSQL = @TEMP + '; '

                                FETCH NEXT

                                FROM ChangeTrackingTables

                                INTO @TEMP

                                set @msg = N'USE ' + @DB + ' ' + @ExecSQL

                                RAISERROR (@msg, 0, 1) WITH NOWAIT

                                EXEC (@msg)

                END

                CLOSE ChangeTrackingTables

                DEALLOCATE ChangeTrackingTables

END