dbo.QBM_PWorkHistoryDelete

1  create   procedure QBM_PWorkHistoryDelete as begin declare @procname nvarchar(256) = object_name(@@procid) declare @Starttime datetime = getutcdate
2() declare @RunningMinutesMax int = 5 declare @AnzahlDialogWatchOperation int = 1 declare @AnzahlDialogProcessChain int = 1 declare @AnzahlDialogProcess
3 int = 1 declare @AnzahlDialogProcessSubst int = 1 declare @AnzahlDialogJournal int = 1 declare @AnzahlJobHistory int = 1 declare @AnzahlCEFMessage int
4 = 1 declare @AnzahlLoginAudit int = 1 declare @BulkCountDialogWatchOperation int declare @TotalCountDialogWatchOperation int declare @BulkCountDialogProcessChain
5 int declare @TotalCountDialogProcessChain int declare @BulkCountDialogProcess int declare @TotalCountDialogProcess int declare @BulkCountJobHistory int
6 declare @TotalCountJobHistory int declare @BulkCountDialogProcessSubst int declare @TotalCountDialogProcessSubst int declare @XUser nvarchar(64) = object_name
7(@@procid) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT
8 OFF BEGIN TRY exec QBM_PSessionContextSet 'XUser', @XUser         if 0 = (select min(sign(len(ISNULL(p.name, '')))) as StartBisHierGeschafft from ( values
9 ('QBMModuleDef')  , ('QBM_FCVStringToInt') , ('QBM_FGIConfigparmValue')  , ('QBM_PSessionErrorAdd')   , ('QBM_FGISessionErrorIsDeadlock')   , ('QBM_FGIMaintenanceRunning'
10) , ('QBM_FGIDBQueueActivityDisabled') , ('QBM_PDeleteBulk') , ('QBM_PJournalShrink')  , ('QBM_PTransactionLevelCheck') , ('QBM_PWaitForSeconds') ) as 
11v (ProcedureName) left outer join sys.objects p with (readpast) on p.name = v.ProcedureName ) begin goto EndLabel end   exec QBM_PDBQueueAgentInfoSet @procname
12  if dbo.QBM_FGIMaintenanceRunning() > ' ' begin goto endLabel end  exec QBM_PTransactionLevelCheck 0, @@procid, 'at start' set deadlock_priority -10  
13set lock_timeout 50     select @BulkCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\JobHistory\Delete\BulkCount'
14), 200) select @TotalCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\JobHistory\Delete\TotalCount'), 10000) select
15 @BulkCountDialogProcess = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\Delete\BulkCount'), 500) select @TotalCountDialogProcess
16 = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\Delete\TotalCount'), 10000) select @BulkCountDialogProcessSubst = @BulkCountDialogProcess
17 select @TotalCountDialogProcessSubst = @TotalCountDialogProcess select @BulkCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue
18( 'Common\ProcessState\ProgressView\Delete\BulkCount'), 200) select @TotalCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 
19'Common\ProcessState\ProgressView\Delete\TotalCount'), 10000) select @BulkCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue
20( 'Common\ProcessState\PropertyLog\Delete\BulkCount'), 200) select @TotalCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue(
21 'Common\ProcessState\PropertyLog\Delete\TotalCount'), 10000) while ( @AnzahlDialogWatchOperation + @AnzahlDialogProcessChain + @AnzahlDialogProcess + 
22@AnzahlDialogProcessSubst + @AnzahlDialogJournal + @AnzahlJobHistory + @AnzahlCEFMessage + @AnzahlLoginAudit) > 0 and DATEDIFF(mi, @Starttime, getutcdate
23()) <= @RunningMinutesMax and dbo.QBM_FGIDBQueueActivityDisabled() = 0 begin set lock_timeout 50     if @AnzahlDialogWatchOperation > 0 begin BEGIN TRY
24 exec @AnzahlDialogWatchOperation = QBM_PDeleteBulk 'DialogWatchOperation', 'ReadyForDeleteOrExport = 1', @BulkCountDialogWatchOperation, @TotalCountDialogWatchOperation
25 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0   begin RAISERROR ('', 18, 1) WITH NOWAIT end 
26else begin select @AnzahlDialogWatchOperation = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcessChain > 0 begin BEGIN TRY exec
27 @AnzahlDialogProcessChain = QBM_PDeleteBulk 'dialogProcesschain', 'ReadyForDeleteOrExport = 1', @BulkCountDialogProcessChain, @TotalCountDialogProcessChain
28 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else
29 begin select @AnzahlDialogProcessChain = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcessSubst > 0 begin BEGIN TRY exec @AnzahlDialogProcessSubst
30 = QBM_PDeleteBulk 'DialogProcessSubstitute', 'ReadyForDeleteOrExport = 1 ', @BulkCountDialogProcessSubst, @TotalCountDialogProcessSubst END TRY BEGIN 
31CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select
32 @AnzahlDialogProcessSubst = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcess > 0 begin BEGIN TRY exec @AnzahlDialogProcess 
33= QBM_PDeleteBulk 'DialogProcess', 'ReadyForDeleteOrExport = 1 ', @BulkCountDialogProcess, @TotalCountDialogProcess END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
34 default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlDialogProcess = 1 exec 
35QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogJournal > 0 begin BEGIN TRY exec @AnzahlDialogJournal = QBM_PJournalShrink END TRY BEGIN CATCH
36 exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlDialogJournal
37 = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlJobHistory > 0 begin BEGIN TRY exec @AnzahlJobHistory = QBM_PDeleteBulk 'JobHistory', 
38'ReadyForDeleteOrExport = 1', @BulkCountJobHistory, @TotalCountJobHistory END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock
39(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlJobHistory = 1 exec QBM_PSessionErrorClean end END CATCH end     if 
40@AnzahlCEFMessage > 0 begin BEGIN TRY exec @AnzahlCEFMessage = QBM_PDeleteBulk 'QBMCEFMessage', 'IsProcessed = 1', 500  , 10000  END TRY BEGIN CATCH exec
41 QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlCEFMessage
42 = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlLoginAudit > 0 begin BEGIN TRY exec @AnzahlLoginAudit = QBM_PLoginAuditShrink END TRY 
43BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin 
44select @AnzahlLoginAudit = 1 exec QBM_PSessionErrorClean end END CATCH end   exec QBM_PWaitForSeconds 0.040 end   if exists (select top 1 1 from jobhistory
45 group by uid_job having count (*) > 1) begin delete JobHistory where UID_JobHistory in (select max(UID_JobHistory) from JobHistory group by UID_Job having
46 count(*) > 1 ) end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin declare @Rethrow varchar
47(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT end else begin exec QBM_PSessionErrorClean end END CATCH endLabel: exec
48 QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R  exec QBM_PDBQueueAgentInfoSet @procname, 1 set lock_timeout
49 -1 return end 
50