dbo.QBM_PJobQueueStatsShrink

1     create   procedure QBM_PJobQueueStatsShrink  as begin   declare @datum datetime select @datum = GetUTCDate() declare @maxAge int declare @MaxDate
2 datetime  declare @DeleteWhereClause nvarchar(max) declare @AnzahlGeloescht int declare @AnzahlGesamt int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext
3('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar
4(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT OFF BEGIN TRY  if exists (select top 1 1 from DialogDatabase where SingleUserProcess <> 0 and 
5IsMainDatabase = 1 ) begin goto GanzEnde end  if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet
6 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser end select @maxAge = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\JobQueueStats\MaxAge'
7), 0) if @maxAge = 0 begin select @maxAge = 30 end select @maxdate = dateadd(dd, @maxage * (-1) , GetUTCDate())  select @DeleteWhereClause = N'convert(datetime,convert(nvarchar(4), jobyear) + N''-'' + right(N''0'' + convert(nvarchar(2), jobmonth), 2) + N''-'' +  right(N''0''+ convert(nvarchar(2), jobday) , 2) , 121)  
8								< '''
9 + convert(nvarchar(64), @maxdate, 121) + '''  /*@maxdate */ ' exec @AnzahlGeloescht = QBM_PDeleteBulk 'jobqueuestats', @DeleteWhereClause, 500 select 
10@AnzahlGesamt += @AnzahlGeloescht  select @GenProcID = newid() exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser',
11 @XUser  insert into JobQueueStats with (tablock) (UID_JobQueueStats, queue , jobYear, jobMonth , jobDay , jobHour , countInserted, countActivated, countDeleted
12 ) select newid(), queue , jobYear, jobMonth , jobDay , jobHour , abs(sum(countInserted)), abs(sum(countActivated)), abs(sum(countDeleted)) from jobqueuestats
13 where uid_jobqueuestats like N'XX%' and jobHour <> datepart(hh, @datum) group by queue , jobYear, jobMonth , jobDay , jobHour  select @DeleteWhereClause
14 = N'uid_jobqueuestats like N''XX%'' 
15								and jobHour <> ' + convert(nvarchar(16) , datepart(hh, @datum)) exec @AnzahlGeloescht = QBM_PDeleteBulk 
16'jobqueuestats', @DeleteWhereClause, 500 select @AnzahlGesamt += @AnzahlGeloescht END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow
17 varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: GanzEnde: exec QBM_PSessionContextSet 'GenProcID'
18, @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return (@AnzahlGesamt) end 
19