Change Tracking Manual Clean up - Ghost Cleanup - DAC

Few items to note: The script references Change Tracking internal objects which are NOT accessible normally and therefore you'll need to run the SP under "ADMIN" (DAC) connection!

Script Usage:

1. Create procedure "sp_ManualChangeTrackingMetaDataCleanupProc" using the script below(included in .txt) in Change Tracking Database.

2. The procedure takes default value of 10,000 for first prameter (@NoOfRowsToDeletePerIteration) and 1 for second parameter (@NoOfIterations), meaning that when SP is executed without specifying either of the two parameters, it will delete 10,000 rows per iteration (from "sys.syscommittab" and corresponding related records from all "sys.change_tracking_*" tables) and does only 1 iteration. Based on number of rows present in CT metadata you may have to increase number of rows per iterations (by increasing value for first parameter) and also increasing value for second parameter to do more iterations.

3. Depending on how big CT metadata has grown, you may want to tweek these two parameter to get optimal performance. @NoOfRowsToDeletePerIteration=10,000 will cause smaller DELETE transaction however may take a lot of time to show some significant reduction in metadata size/rows. In such scenario, you may want to bump @NoOfRowsToDeletePerIteration to 100,000 or even 500,000 to delete more rows per transaction which will results in bigger transactions but will remove data more aggressively. Note: If you start seeing severe blocking due to this SP, you may want to reduce this number down.

//**

Script Usage:

1.Create procedure "sp_ManualChangeTrackingMetaDataCleanupProc" using the script below in Change Tracking Database. 

2.The procedure takes default value of 10,000 for first prameter (@NoOfRowsToDeletePerIteration) and 1 for second parameter (@NoOfIterations), meaning that when SP is executed without specifying either of the two parameters, it will delete 10,000 rows per iteration (from "sys.syscommittab" and corresponding related records from all "sys.change_tracking_*" tables) and does only 1 iteration. Based on number of rows present in CT metadata you may have to increase number of rows per iterations (by increasing value for first parameter) and also increasing value for second parameter to do more iterations.

3.Depending on how big CT metadata has grown, you may want to tweek these two parameter to get optimal performance. @NoOfRowsToDeletePerIteration=10,000 will cause smaller DELETE transaction however may take a lot of time to show some significant reduction in metadata size/rows. In such scenario, you may want to bump @NoOfRowsToDeletePerIteration to 100,000 or even 500,000 to delete more rows per transaction which will results in bigger transactions but will remove data more aggressively. If you start seeing severe blocking due to this SP, you may want to reduce this number down

Note: The script references Change Tracking internal objects which are NOT accessible normally and therefore you'll need to run the SP under "ADMIN" (DAC) connection.

**//

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManualChangeTrackingMetaDataCleanupProc]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_ManualChangeTrackingMetaDataCleanupProc]

GO

Create procedure sp_ManualChangeTrackingMetaDataCleanupProc (@NoOfRowsToDeletePerIteration int=10000, @NoOfIterations int=1)

As

Declare @intCount int

Set @intCount=0

Declare @CTSideTables sysname

Declare @stmt nvarchar(1000)

Declare @CTsideTableRowCount int

Declare @syscommittabRowCount int

Declare @intRetention int

Declare @tintRetentionPeriodUnits tinyint

Declare @dtCutOffDate Datetime

SET NOCOUNT ON

--Setting deadlock priority low, so this script in choosen as victim in case of deadlock

SET DEADLOCK_PRIORITY LOW

Create Table #xdes_idToClean (xdes_id bigint)

--Query Retention Period for current DB

Select @intRetention=retention_period,@tintRetentionPeriodUnits=retention_period_units from sys.change_tracking_databases where database_id = DB_ID() 

If @intRetention = NULL 

Begin

Print 'Exiting Procedure: Retention Period cannot be NULL'

Return

End

--Save cleanup watermark

Declare @CleanupWatermmark BigInt

SELECT @CleanupWatermmark = CAST((select value from sys.sysobjvalues where valclass=7 and objid=1003) as BigInt)

PRINT 'Cleanup Watermark = ' + CAST(@CleanupWatermmark as nvarchar(30))

--Query sys.syscommittab for commit_time corresponding to sys.sysobjvalues.objid=1003

Declare @dtCommit_time Datetime

Select @dtCommit_time=commit_time from sys.syscommittab where commit_ts =@CleanupWatermmark

--Calculate rentention period cut-off time in UTC

--retention_period_units (@tintRetentionPeriodUnits): 1 = Minutes, 2 = Hours, 3 = Days

if @tintRetentionPeriodUnits = 1

--Minutes

Select @dtCutOffDate=Dateadd(mi,-@intRetention,getUTCdate())

else if @tintRetentionPeriodUnits = 2

--Hours

Select @dtCutOffDate=Dateadd(hh,-@intRetention,getUTCdate())

else if @tintRetentionPeriodUnits = 3

--Days

Select @dtCutOffDate=Dateadd(dd,-@intRetention,getUTCdate())

--Print CutOffDate

if @dtCutOffDate is NULL

Print 'Calculated Cut-Off Date = NULL'

else

Print 'Calculated Cut-Off Date = ' + Convert (varchar(30), @dtCutOffDate)

--Print sys.sysobjvalues.objid=1003 commit_time

if @dtCommit_time is NULL

Print 'sys.sysobjvalues Commit_time = NULL'

else

Print 'sys.sysobjvalues Commit_time = ' + Convert (varchar(30), @dtCommit_time)

--Exit if @dtCutOffDate or @dtCommit_time is NULL

If @dtCutOffDate Is NULL or @dtCommit_time is NULL

Begin

Print 'Exiting Procedure: Since Either Retention Cut-Off Period OR Cut-Off Date is NULL'

Return

End

--Exit procedure if retention cut-off period is LESS THAN sys.sysobjvalues.objid=1003 commit_time

If @dtCutOffDate<@dtCommit_time

Begin

Print 'Exiting Procedure: Since Retention Cut-Off Period is LESS THAN sys.sysobjvalues.objid=1003 commit_time'

Return

End

--If we get here we are beyond the retention period, its safe to proceed with cleanup.

While (@intCount<@NoOfIterations)

Begin

--Truncate table 

Truncate table #xdes_idToClean

--Insert top @NoOfRowsToDeletePerIteration commit_ts into temp table

Insert into #xdes_idToClean Select TOP (@NoOfRowsToDeletePerIteration) xdes_id FROM sys.syscommittab 

where commit_ts < @CleanupWatermmark order by commit_ts

--Declare cursor to loop through CT side tables.

Declare curCTSideTables Cursor

For Select name from sys.internal_tables where internal_type=209

Open curCTSideTables 

Fetch Next From curCTSideTables Into @CTSideTables

While @@Fetch_Status=0

Begin

Set @CTsideTableRowCount=1

While (@CTsideTableRowCount>0)

Begin

Select @stmt = N'delete top (' + rtrim(Convert(char(11),@NoOfRowsToDeletePerIteration)) + ') from sys.' + @CTSideTables + ' where sys_change_xdes_id in (Select xdes_id from #xdes_idToClean)'

exec sp_executesql @stmt

Select @CTsideTableRowCount = @@rowcount

Print 'Deleted ' + rtrim(Convert(char(11),@CTsideTableRowCount)) + ' rows from sys.' + @CTSideTables

End

Fetch Next From curCTSideTables Into @CTSideTables

End

Close curCTSideTables

Deallocate curCTSideTables

--Now that data from side tables is removed, we can safely remove data from syscommittab

--DELETE TOP (@NoOfRowsToDelete) FROM sys.syscommittab where commit_time < @CleanupDate order by commit_time

DELETE FROM sys.syscommittab where xdes_id in (Select xdes_id from #xdes_idToClean)

Set @syscommittabRowCount=@@rowcount

Print 'Deleted ' + rtrim(Convert(char(11),@syscommittabRowCount)) + ' rows from sys.syscommittab' 

If @syscommittabRowCount=0

Begin

--If 0 rows deleted from sys.syscommittab then stop procedure

Set @intCount=@NoOfIterations

Print '0 rows for deletion in sys.syscommittab, stopping processing'

End

Else

Set @intCount=@intCount+1

End

Drop table #xdes_idToClean

/************************************/