You can remove SQL Server change tracking from the current database when your MDriven application no longer needs to detect changes made directly by another system.

SQL Server change tracking is used when a non-MDriven system reads or writes the same SQL database and MDriven must discover the changed rows. It is separate from the MDrivenServer internal dirty list. Remove it only after you have confirmed that no MDrivenServer server-side job still depends on change-tracking versions or CHANGETABLE queries.

Before you begin

SQL Server change tracking can be enabled for the database and for selected tables. You must disable it on every tracked table before disabling it for the database.

Review the MDriven implementation before you make this change:

• Check the server-side jobs described in Documentation:SQL Server change tracking. In particular, review jobs that get CHANGE_TRACKING_CURRENT_VERSION() or invalidate objects through CHANGETABLE.
• Confirm that external systems no longer make changes that MDriven must discover through SQL Server change tracking.
• Run the script in the target database. The procedure operates on the database in which you create and execute it.

For example, if a legacy integration previously updated [dbo].[Order] directly, MDriven may have used change tracking to invalidate affected Order objects. Do not remove tracking while that integration and its invalidation job are still required.

Remove tracking from all tracked tables

Run the following script in the SQL Server database from which you want to remove change tracking. It finds the currently tracked tables through sys.change_tracking_tables, includes each table schema, and then disables change tracking for the current database.

CREATE PROCEDURE [dbo].[spTurnOffChangeTracking]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SchemaName sysname;
    DECLARE @TableName sysname;
    DECLARE @Command nvarchar(max);

    DECLARE ChangeTrackingCursor CURSOR FOR
        SELECT s.name, t.name
        FROM sys.change_tracking_tables AS ctt
        INNER JOIN sys.tables AS t
            ON t.object_id = ctt.object_id
        INNER JOIN sys.schemas AS s
            ON s.schema_id = t.schema_id
        ORDER BY s.name, t.name;

    OPEN ChangeTrackingCursor;
    FETCH NEXT FROM ChangeTrackingCursor INTO @SchemaName, @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Command =
            N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
            N' DISABLE CHANGE_TRACKING;';
        EXEC (@Command);

        FETCH NEXT FROM ChangeTrackingCursor INTO @SchemaName, @TableName;
    END;

    CLOSE ChangeTrackingCursor;
    DEALLOCATE ChangeTrackingCursor;

    SET @Command =
        N'ALTER DATABASE ' + QUOTENAME(DB_NAME()) +
        N' SET CHANGE_TRACKING = OFF;';
    EXEC (@Command);
END;

Execute the procedure:

EXEC [dbo].[spTurnOffChangeTracking];

The procedure does the following:

1. Reads the tracked tables from sys.change_tracking_tables.
2. Runs ALTER TABLE ... DISABLE CHANGE_TRACKING once for each tracked table.
3. Runs ALTER DATABASE ... SET CHANGE_TRACKING = OFF for the database in the current SQL connection.

Check which tables are tracked

Use this query before the change to review the tables that will be affected. Run it again after the procedure to confirm that it returns no rows.

SELECT
    s.name AS Schema_name,
    t.name AS Table_name
FROM sys.change_tracking_tables AS ctt
INNER JOIN sys.tables AS t
    ON t.object_id = ctt.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
ORDER BY
    s.name,
    t.name;

For example, a result containing dbo and Order means that the procedure will execute ALTER TABLE [dbo].[Order] DISABLE CHANGE_TRACKING before turning tracking off for the database.

After removal

Update or remove the MDrivenServer server-side jobs that used SQL Server change tracking. A job that compares CurrentVersionNumberFromChangeTracking with LastHandledVersionNumberFromChangeTracking, or that queries CHANGETABLE, is part of the configuration described in Documentation:SQL Server change tracking. It should not remain active after tracking is removed.

If you later need MDriven to detect direct updates from an external system again, enable tracking only for the database tables that the external system can change. The setup pattern, model attributes, ViewModels, EAL actions, and invalidation jobs are documented in Documentation:SQL Server change tracking.

See also

• Documentation:SQL Server change tracking
• HowTos:Use SqlServer with MDriven Turnkey
• HowTos:Reverse Engineer a Database
• HowTos:Recreate the SQL Database