Change the Stored Procedure of "Task_clean_system_flow_log"

 Attachement didnt work, so here the custom code

create procedure [dbo].[task_clean_system_flow_log] 
as 
begin 

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

    --control_proc_id:      task_clean_system_flow_log 
    --template_id:          task_clean_system_flow_log 
    --prog_object_item_id:  task_clean_system_flow_log 
    --template_description:  

drop table if exists ##temp_log 

    declare   
      @system_flow_log_retention int   
      select @system_flow_log_retention = system_flow_log_retention   
      from global_settings   

      --Make sure @system_flow_log_retention = at least 1 
      if isnull(@system_flow_log_retention, 0) = 0   
      begin   
         set @system_flow_log_retention = 1   
      end 

declare @aantal int = @system_flow_log_retention 
declare @count int 
declare @verwijder int 
set @count = (select count(*) from dbo.process_flow_schedule_log)  
set @verwijder = @count  - @aantal 

if @verwijder < 0  
      begin   
         set @verwijder = 0   
      end 

select top (select @verwijder) * into ##temp_log from dbo.process_flow_schedule_log order by log_id asc 

      --Delete log items based on retention value 
  delete from process_flow_schedule_log where log_id in( 
  SELECT l.log_id   
         FROM process_flow_schedule_log l 
 JOIN ##temp_log as t 
 ON l.log_id = t.log_id 
         WHERE not exists   
            (  
            SELECT 1   
            FROM system_flow_excluded_from_log_clean_up s   
            WHERE s.project_id = l.project_id   
           AND s.project_vrs_id = l.project_vrs_id   
           AND s.gui_appl_id = l.gui_appl_id   
           AND s.process_flow_id = l.process_flow_id  
            )   
        AND (l.end_date_time is not null   
         OR l.abandoned = 1) --Make sure the system flow has finished or abandoned its scheduled   
            -- run 

        AND l.log_id <   
            (  
            SELECT l2.log_id   
            FROM process_flow_schedule_log l2 
JOIN ##temp_log as t2 
   			ON l2.log_id = t2.log_id 

            WHERE l2.project_id = l.project_id   
           AND l2.project_vrs_id = l.project_vrs_id   
           AND l2.gui_appl_id = l.gui_appl_id   
           AND l2.process_flow_id = l.process_flow_id   
            ORDER BY log_id desc offset 0 rows --Keep first X amount of   
            FETCH first 1 rows only  
            ) 
    ) 
    --Also delete agent check in log items 
    ;with agent_check_in_items as ( 
        select l.check_in_time_utc 
        from agent_check_in l 
        where l.check_in_time_utc < (select l2.check_in_time_utc 
                                     from agent_check_in l2 
                                     order by check_in_time_utc desc 
                                     offset @system_flow_log_retention rows --Keep first X amount of rows 
                                     fetch first 1 rows only) 
    ) 
    delete a 
    from agent_check_in_items a 

end 
GO

Thanks for the suggestion, we'll take a look at it. I do feel that this idea should have been a ticket for us 😉

Ah oke, next time i will send it as a ticket.

Do you have a status update of the "review”?

Hi,

Due to the end of our development sprint and other reasons we have been unable to discuss this internally yet. I hope to give a status update next week.

Hi, We would like to solve this via a ticket in TCP. Could you create a ticket for this issue and attach an execution plan of the query that is not performing?

Today il make a ticket an with execution plan