dbo.QBM_PJobQueueDelete

1  create   procedure QBM_PJobQueueDelete ( @RowLimit int = 50  , @MaxLimit int = 30000  ) as begin declare @Trees QBM_YParameterList declare @TreePortion
2 QBM_YParameterList declare @einRuck int  declare @LaufDelete int = 0 declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @justNow datetime
3 = getUTCDate() declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'I' declare @DebugMessage nvarchar(1000) declare @Start datetime declare @ende
4 datetime declare @diff int declare @ExpectedRuntime float = 1000.0  declare @HistoryEntries QBM_YJobQueueShadow declare @anzahl int SET XACT_ABORT OFF
5 BEGIN TRY set lock_timeout 50 select @anzahl = dbo.QBM_FGITableCountAll('JobQueue') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT
6 = 0  begin begin transaction BEGIN TRY  update JobQueue set MinutesToDefer = q.MinutesToDefer from JobQueue q with (tablockx) END TRY BEGIN CATCH select
7 @anzahl = 1  END CATCH  if dbo.QBM_FGITableCountAll('JobQueue') = 0 and @anzahl = 0 begin BEGIN TRY  truncate table QBMElementAffectedByJob truncate table
8 JobQueue  commit select @LaufDelete = 1 END TRY BEGIN CATCH   rollback END CATCH end else begin  rollback end end if @LaufDelete = 1 begin goto endLabel
9 end normal: insert into @HistoryEntries ( ComponentAssembly, ComponentClass, EndedAt, JobChainName, ParamIN, Priority, Queue, StartAt, TaskName, UID_Job
10,  UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, UID_JobError, UID_JobSuccess, GenProcID, UID_JobOrigin, BasisObjectKey
11, ErrorMessages  , LogMode, Ready2EXE)  select q.ComponentAssembly, q.ComponentClass, isnull(q.xdateupdated, GETUTCDATE()) as EndedAt, q.JobChainName, 
12dbo.QBM_FCVJobParamDeflate (q.ComponentClass, q.TaskName, q.ParamIN), q.Priority, q.Queue, q.StartAt, q.TaskName, q.UID_Job,  q.UID_Tree, q.WasError, q.XDateInserted
13, q.XDateUpdated, q.XUserInserted, q.XUserUpdated, q.IsRootJob, e.UID_Job, s.UID_Job, q.GenProcID, q.UID_JobOrigin, q.BasisObjectKey, q.ErrorMessages ,
14 q.LogMode , q.Ready2EXE  from ( select distinct top (@MaxLimit) qt.UID_Tree from JobQueue qt with (readpast) where qt.Ready2EXE = N'HISTORY' ) t join 
15JobQueue q with (readpast) on t.UID_Tree = q.UID_Tree left outer join JobQueue e with (readpast) on q.UID_Tree = e.UID_Tree and q.UID_JobError = e.UID_Job
16 and e.Ready2EXE in ( N'FINISHED', N'HISTORY') left outer join JobQueue s with (readpast) on q.UID_Tree = s.UID_Tree and q.UID_JobSuccess = s.UID_Job and
17 s.Ready2EXE in ( N'FINISHED', N'HISTORY') where  q.Ready2EXE in ( N'FINISHED', N'HISTORY') if @@ROWCOUNT = 0 begin goto loeschen end insert into @Trees
18 (Parameter1 , ContentFull ) select distinct q.UID_Tree , '' from @HistoryEntries q  if exists (select top 1 1 from @Trees t join JobTreeParamColl co with
19 (readpast) on co.UID_Tree = t.Parameter1  ) begin  update @Trees set ContentFull  = dbo.QBM_FCVJobParameterToString(t.Parameter1 ) from @Trees t  update
20 @Trees set ContentFull  = SUBSTRING(t.ContentFull , 2 , LEN(t.ContentFull ) -1) from @Trees t where t.ContentFull  > ' ' update @Trees set ContentFull
21  = '' from @Trees t where t.ContentFull  is null end insert into JobHistory ( ComponentAssembly, ComponentClass, EndedAt, JobChainName, ParamIN, Priority
22, Queue, StartAt, TaskName, UID_Job, UID_JobHistory, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, UID_JobError
23, UID_JobSuccess, GenProcID, UID_JobOrigin, BasisObjectKey, ErrorMessages  , LogMode)  select q.ComponentAssembly, q.ComponentClass, q.EndedAt, q.JobChainName
24, dbo.QBM_FCVJobParamDeflate (q.ComponentClass, q.TaskName, q.ParamIN + t.ContentFull ), q.Priority, q.Queue, q.StartAt, q.TaskName, q.UID_Job, newid()
25, q.UID_Tree, q.WasError, q.XDateInserted, q.XDateUpdated, q.XUserInserted, q.XUserUpdated, q.IsRootJob, q.UID_JobError, q.UID_JobSuccess, q.GenProcID,
26 q.UID_JobOrigin, q.BasisObjectKey, q.ErrorMessages , q.LogMode  from @HistoryEntries q join @Trees t on q.UID_Tree = t.Parameter1  where not exists (select
27 top 1 1 from JobHistory h where h.UID_Tree = q.UID_Tree and h.UID_Job = q.UID_Job )  loeschen:  insert into @Trees (Parameter1 ) select distinct top (@MaxLimit
28) qt.UID_Tree from JobQueue qt with (readpast) where ready2exe = N'DELETE' select @LaufDelete = 1 select @einRuck = @RowLimit while @LaufDelete > 0 begin
29 select @Start = getutcdate() delete  @TreePortion  insert into @TreePortion (Parameter1 ) select top (@einRuck) t.Parameter1 from @Trees t where t.HasContentFull
30 = 0  select @LaufDelete = @@ROWCOUNT if @LaufDelete = 0 begin continue end update @Trees set HasContentFull = 1  from @Trees t join @TreePortion p on 
31t.Parameter1 = p.Parameter1  BEGIN TRY delete Jobqueue where UID_Tree in (select t.Parameter1  from @TreePortion t ) select @AnzahlLoesch = @@rowcount 
32END TRY BEGIN CATCH  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate
33() select @diff = DATEDIFF(ms, @start, @ende) select @einRuck = case when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime
34 then @RowLimit else convert(int, @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff)) end  end  if (@AnzahlLoeschGesamt > 0) begin if 
35('1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats')) begin  insert into JobQueueStats (UID_JobQueueStats, queue , jobYear, jobMonth , jobDay , jobHour
36 , countInserted, countActivated, countDeleted ) select 'XX'+convert(varchar(64), newid()), N'' , year(@justNow) ,month(@justNow) ,day( @justNow) , datepart
37(hh,@justNow) , 0, 0, (-1) * @AnzahlLoeschGesamt end end  if @DebugSwitch > 0 begin select @DebugMessage = 'count entries in Jobqueue deleted ' + STR(@AnzahlLoeschGesamt
38) exec QBM_PJournal @debugmessage, @@PROCID, 'I', @debuglevel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) 
39= dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt) end 
40