Hi Andre,

Depending which platform version you are currently using, the Software Factory will automatically clean up old archived branches and their history data periodically. Release notes.

https://docs.thinkwisesoftware.com/blog/2024_3#models---archived-branches-and-history-data

From 2024.2, a clean-up task is present in the Advanced menu:

https://docs.thinkwisesoftware.com/docs/sf/overview#branch-maintenance

Hope this helps!

Hi Mark, 

We are on 2024.2 but I can't find a task to clean up in the SF Advanced menu as described. The only available clean up task is ‘Clean up redundant rights’. Do I need special rights? 

Ah my bad, I thought be added this in 2024.2. Is upgrading to 2024.3 a viable alternative at the moment?

Not at this moment. Could the task (script) work for 2024.2? 

It is, with a bit of tweaking.

Disclaimer, do NOT run this while anyone is working with the Software Factory

This code removes all archived branches, and thereafter cleans up history data for each non-existing model and branch. This requires the base table and hostry table to become unlinked for a while.

You can filter on a particular model and/or branch if desired, or solely clean up the history data of removed models/branches.

-- Start of script - Code designed for SF 2024.2
/* task_clean_up_model_history as code */
declare
   @delete_archived_branches         "no_yes"      ,
   @model_id_filter                  "model_id"    ,
   @branch_id_filter                 "branch_id"   

select 
    @delete_archived_branches = 1, -- Will delete all archived branches
    @model_id_filter = null,
    @branch_id_filter = null


-- Do not count affected rows for performance
SET NOCOUNT ON;

--control_proc_id:      task_clean_up_model_history
--template_id:          delete_archive
--prog_object_item_id:  delete_archive
--template_description: 

declare @archived_branches table
(
    model_id model_id,
    branch_id branch_id
)

declare @merge_sessions table
(
    merge_session_id id
)

-- Load all archived branches to delete
if @delete_archived_branches = 1
begin
    -- Cache branches to process    
    insert into @archived_branches
    (
        model_id,
        branch_id
    )
    select 
        b.model_id, 
        b.branch_id
    from branch b
    where b.archived = 1
      and b.protected = 0
      and (@model_id_filter is null or b.model_id = @model_id_filter)
      and (@branch_id_filter is null or b.branch_id = @branch_id_filter)
end

-- Stop if there are no branches to delete
if not exists (select 1 from @archived_branches)
begin
    return
end

-- Start deleting archived branches (if any)
declare @archived_model_id model_id
declare @archived_branch_id branch_id
declare @archived_merge_session_id id
declare @progress_text varchar(300)

declare c_branches cursor fast_forward for
select model_id, branch_id
from @archived_branches

open c_branches
fetch next from c_branches into @archived_model_id, @archived_branch_id

-- Delete one by one, after deleting the related merge sessions
while @@fetch_status = 0
begin

    -- Find and delete merge sessions
    insert into @merge_sessions
    (
        merge_session_id
    )
    select m.merge_session_id
    from merge_session m
    where m.model_id = @archived_model_id
      and @archived_branch_id in (m.origin_branch_id, m.source_branch_id, m.target_branch_id)

    declare c_merge_sessions cursor fast_forward for
    select merge_session_id
    from @merge_sessions

    open c_merge_sessions
    fetch next from c_merge_sessions into @archived_merge_session_id

    while @@fetch_status = 0
    begin
        set @progress_text = '<text>Deleting merge session ' + cast(@archived_merge_session_id as varchar) + ' for archived branch "' + @archived_model_id + '" / "' + @archived_branch_id + '".</text>'
        exec tsf_send_progress 'default', @progress_text, 0


        exec task_delete_merge_session @archived_merge_session_id
        fetch next from c_merge_sessions into @archived_merge_session_id
    end

    close c_merge_sessions
    deallocate c_merge_sessions

    delete @merge_sessions

    set @progress_text = '<text>Deleting archived branch "' + @archived_model_id + '" / "' + @archived_branch_id + '"</text>'
    exec tsf_send_progress 'default', @progress_text, 0

    -- Unlock the branch first
    exec task_reset_branch_lock @archived_model_id, @archived_branch_id

    -- Delete the branch
    exec task_delete_branch @archived_model_id, @archived_branch_id

    fetch next from c_branches into @archived_model_id, @archived_branch_id
end

close c_branches
deallocate c_branches

--control_proc_id:      task_clean_up_model_history
--template_id:          clean_history
--prog_object_item_id:  clean_history
--template_description: 

declare @scripts table 
(
    order_no int, 
    perc int, 
    script nvarchar(max)
)

-- Build a script per temporal table with a model- and branch id to clean the history
insert into @scripts
select 
    row_number() over (order by t.name, s.order_no),
    ntile(99) over (order by t.name, s.order_no),
    s.smt
from sys.tables t
cross apply (
       select 1, N'tsf_send_progress ''default'', ''<text>Processing table "' + t.name + '"</text>'', @percentage'
 union select 3, N'alter table "' + t.name + '" set (system_versioning = off)'
 union select 4, N'while @@rowcount > 0' + 
                  'begin ' +
                  'delete top (50000) h from ' + t.name + '_history h ' + 
                  'where (@model_id is null or h.model_id = @model_id) ' + 
                  '  and (@branch_id is null or h.branch_id = @branch_id) ' +
                  '  and not exists(select 1 from branch b where b.model_id = h.model_id and b.branch_id = h.branch_id) ' +
                  'end '
 union select 5, N'alter table "' + t.name + '" set (system_versioning = on (history_table = dbo."' + t.name + '_history", DATA_CONSISTENCY_CHECK = OFF))'
) s (order_no, smt)
where t.temporal_type = 2
  and exists(
    select 1
    from sys.all_columns c
    where c.object_id = t.object_id
      and c.column_id = 1
      and c.name = 'model_id')
  and exists(
    select 1
    from sys.all_columns c
    where c.object_id = t.object_id
      and c.column_id = 2
      and c.name = 'branch_id')

declare @script nvarchar(max)
declare @perc int

declare c_scripts cursor fast_forward for
select script, perc
from @scripts
order by order_no

open c_scripts
fetch next from c_scripts into @script, @perc

while @@FETCH_STATUS = 0
begin
    begin transaction

    exec sp_executesql @script, 
            N'@model_id varchar(100), @branch_id varchar(100), @percentage int', 
            @model_id = @model_id_filter, @branch_id = @branch_id_filter, @percentage = @perc

    commit transaction
    fetch next from c_scripts into @script, @perc
end

close c_scripts
deallocate c_scripts
-- End of script