SQL PI Change Tracking Table Grows and Causes PI Repository Transaction Log Exhaustion (4381989)

Workaround

Collect the output from this SQL Query if working with Support

SELECT inserted_time, count(1) as cnt
from [dbo].[pass_change_tracking_item]
group by inserted_time
order by inserted_time

SELECT
    t.NAME AS TableName,
    SUM(a.total_pages) * 8  / 1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB,
	MAX(p.rows) AS RowCnt
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME = 'pass_change_tracking_item'
GROUP BY t.Name

SELECT inserted_time, count(1) as cnt
from [dbo].[pass_ct_partitions_state]
group by inserted_time
order by inserted_time

SELECT
    t.NAME AS TableName,
    SUM(a.total_pages) * 8  / 1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB,
	MAX(p.rows) AS RowCnt
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME = 'pass_ct_partitions_state'
GROUP BY t.Name

If the pass_change_tracking_item table has already grown very large:

1. Stop the PI agent.
2. Run the manual cleanup script (recreate_ct_item_with_last_data.sql) which:
   • copies only the last 90 days of data into a new side table
   • switches (renames) the old table out
   • drops the large, original table
3. Start the PI agent again after the cleanup completes.

This avoids massive DELETE operations and safely reduces table size.

Note: Users must be logged into Supportlink to download this file.

If the pass_ct_partitions_state table has already grown very large:

1. Stop the PI agent.
2. Run the manual cleanup script (recreate_ct_partitions_with_last_data.sql) which:
   • copies only the last 90 days of data into a new side table
   • switches (renames) the old table out
   • drops the large, original table
3. Start the PI agent again after the cleanup completes.

This avoids massive DELETE operations and safely reduces table size.

Note: Users must be logged into Supportlink to download this file.

Current Recommendation

If the environment is impacted repeatedly, you may disable change tracking temporarily until the fix is released. This will stop new data from being added to the Change Tracking table but it will not stop the purge process which requires manual assistance to help clear out the table.

Disabling change tracking is details in Knowledgebase article 4231553.

Could the Change Tracking table be truncated?

Status