-- Mudar configuração do banco
USE [<BANCO_DE_DADOS>] SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
-- Criando índice na TBPROCESSDATA para melhorar a performance
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[TBPROCESSDATA]') AND type=2 AND name=N'PK_TBPROCESSDATA_2') CREATE UNIQUE NONCLUSTERED INDEX [PK_TBPROCESSDATA_2] ON [dbo].[TBPROCESSDATA] ([PROCESSDATAID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY] GO
-- Criando estatística para melhorar o desempenho da limpeza
CREATE STATISTICS [_dt_stat_tbprocess] ON [dbo].[TBPROCESS]([LOGDOCID], [PROCESSID]) GO
-- Habilitando o Ad Hoc Distributed Queries
sp_configure 'show advanced options', 1 RECONFIGURE; GO
sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE; GO
-- Criar tabelas temporárias
CREATE TABLE [dbo].[TmpDocInvalidsProcessIDs]( [ProcessID] [bigint] NOT NULL, [ProcessDataID] [bigint] NULL, PRIMARY KEY CLUSTERED ([ProcessID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO
CREATE TABLE [dbo].[TmpLogsDocIds]( [LOGDOCID] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ([LOGDOCID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO
CREATE TABLE [dbo].[TmpLotIDs]( [LOTID] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ([LOTID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO
CREATE TABLE [dbo].[TmpProcessIds]( [ProcessId] [bigint] NOT NULL, [RegistryProcessID] [bigint] NULL PRIMARY KEY CLUSTERED ([ProcessId] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO
-- Criar tabelas de logs
CREATE TABLE [dbo].[TBLOGLIMPDOCS]( [IdLimpDocs] [int] IDENTITY(1,1) NOT NULL, [HorIniLim] [datetime] NOT NULL, [DiaVidDoc] [int] NULL, [TotDiaExc] [int] NULL, [BulkLogged] [int] NULL, [LimTLog] [int] NULL, [TipLimTLog] [varchar](4) NULL, [DirBkpTLog] [varchar](100) NULL, [NomJobTLog] [varchar](50) NULL, [PreserveRejecteds] [int] NULL, [TotExcTbLogFilterDocument] [int] NULL, [TotExcTbProcess] [int] NULL, [TotExcTbProcessData] [int] NULL, [TotLoopDocInvalidos] [int] NULL, [TotExcTbEvent] [int] NULL, [TotExcTbProcessLots] [int] NULL, [TotExcTbLots] [int] NULL, [TotExcTbLogDocumentPrinted] [int] NULL, [TotExcTbImpression] [int] NULL, [TotExcTbRelatedDocument] [int] NULL, [TotExcTbLogDocMessage] [int] NULL, [TotExcTbLogDocumentAction] [int] NULL, [TotExcTBENTRYDOCUMENT] [int] NULL, [TotExcTbLogDocument] [int] NULL, [TotExcTbLogDocumentStatus] [int] NULL, [TotLoopDocumentos] [int] NULL, [HorFimLim] [datetime] NULL, [StatusAtual] [varchar](100) NULL) ON [PRIMARY]
CREATE TABLE [dbo].[TBLOGMSGS]( [IdLogMSg] [bigint] IDENTITY(1,1) NOT NULL, [IdLOGLIMPDOCS] [int] NOT NULL, [HorLog] [datetime] NOT NULL, [TxtMsg] [varchar](200) NULL) ON [PRIMARY] GO
CREATE TABLE [dbo].[TBPARLIMDOCS]( [DiaVidDoc] [int] NOT NULL, [QtdRegLimLoo] [int] NOT NULL, [TemAtrLoo] [time](7) NOT NULL, [TotDiaExc] [int] NOT NULL, [BulkLogged] [int] NOT NULL, [LimTLog] [int] NOT NULL, [TipLimTLog] [varchar](4) NULL, [DirBkpTLog] [varchar](100) NULL, [NomJobTLog] [varchar](50) NULL, [MsgLog] [int] NULL, [PreserveRejecteds] [int] NOT NULL) ON [PRIMARY] GO
ALTER TABLE [dbo].[TBPARLIMDOCS] ADD CONSTRAINT [DF_TBPARLIMDOCS_MsgLog] DEFAULT ((0)) FOR [MsgLog] GO
-- Criar procedimento para escrita de logs
/****** Object: StoredProcedure [dbo].[ESCREVE_LOG] Script Date: 09/23/2012 22:19:21 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ESCREVE_LOG]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[ESCREVE_LOG] GO
-- Procedimento de escrita de log
CREATE PROCEDURE [dbo].[ESCREVE_LOG] @IdLimDocs int, @TxtLog varchar(500) AS DECLARE @MsgLog int BEGIN SET NOCOUNT ON; SELECT @MsgLog = MsgLog FROM TBPARLIMDOCS IF @MsgLog = 1 INSERT INTO TBLOGMSGS (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), @TxtLog) END GO
/****** Object: StoredProcedure [dbo].[LIMPA_TRANSACTION_LOG] Script Date: 09/23/2012 22:26:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LIMPA_TRANSACTION_LOG]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
-- Procedimento de limpeza do transaction log, caso [TBPARLIMDOCS].[LimTLog] = 1
CREATE PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG] @IdLimDocs int AS DECLARE @TipLimTLog VARCHAR(4), @DirBkpTLog VARCHAR(100), @NomJobTLog VARCHAR(50), @arquivo VARCHAR(100), @DBName VARCHAR(50), @RunStatus int
BEGIN SET NOCOUNT ON; SELECT @TipLimTLog = TipLimTLog FROM TBPARLIMDOCS SELECT @DirBkpTLog = DirBkpTLog FROM TBPARLIMDOCS SELECT @NomJobTLog = NomJobTLog FROM TBPARLIMDOCS SELECT @DBName = DB_NAME()
UPDATE TBLOGLIMPDOCS SET TipLimTLog = @TipLimTLog, DirBkpTLog = @DirBkpTLog, NomJobTLog = @NomJobTLog WHERE IdLimpDocs = @IdLimDocs
UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando Transaction Log via '+@TipLimTLog
EXEC ESCREVE_LOG @IdLimDocs, 'Iniciou backup'
IF @TipLimTLog = 'PROC' -- gera backup do transaction log no diretório especificado com nome concatenado BEGIN SET @arquivo = @DirBkpTLog + '\' + @DBName + '_' + replace(replace(replace(convert(varchar(50), getdate(), 120),'-',''),':',''),' ','') + '.trn' BACKUP LOG @DBName TO DISK=@arquivo END
-- Executa job destinado para backup do transaction log, caso o status = 1 (rodando) a procedure aguarda a conclusão
ELSE BEGIN -- Verifica o status do job informado
SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q WHERE NAME = 'Backup TLog.Subplan_1')
-- Enquando o job estiver executando, ele aguarda 5 segundos e verificad novamente o status
WHILE @RunStatus = 1 BEGIN UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Aguardando execução do job'+@NomJobTLog WAITFOR DELAY '00:00:05' SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q WHERE NAME = 'Backup TLog.Subplan_1') END
-- Executa o job informado
EXEC msdb.dbo.sp_start_job @NomJobTLog;
-- Aguarda o job encerrar para voltar para a procedure de limpeza
SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q WHERE NAME = 'Backup TLog.Subplan_1')
WHILE @RunStatus = 1 BEGIN UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Aguardando execução do job'+@NomJobTLog WAITFOR DELAY '00:00:05' SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q WHERE NAME = 'Backup TLog.Subplan_1') END END END GO
/****** Object: StoredProcedure [dbo].[LIMPA_REGISTROS_EXPIRADOS] Script Date: 09/23/2012 22:42:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LIMPA_REGISTROS_EXPIRADOS]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[LIMPA_REGISTROS_EXPIRADOS] GO
CREATE PROCEDURE [dbo].[LIMPA_REGISTROS_EXPIRADOS] AS DECLARE @DBName varchar(50), @IdLimDocs INT, @diasdevida INT, @totalregistro INT, @totaldedias INT, @bulkLogged INT, @limpalog INT, @qtdloop INT, @sql varchar(7000), @BaseComEventos BIT, @RowCount INT, @CountLoop INT, @totalLimpezaLoop BIGINT, @Debug BIT, @tempodeatraso char(8), @tiplimlog varchar(4), @minDocDate datetime, @minLotDate datetime, @minDocDate_loop datetime, @minLotDate_loop datetime, @MsgLog varchar(200), @InicioExec datetime, @minProcessDate datetime, @maxProcessDataId INT, @countWsRegs INT, @preserveRejecteds INT
SET NOCOUNT ON SET XACT_ABORT ON Select @DBName = DB_NAME()
BEGIN TRY set ansi_nulls off
-- Carrega as variáreis de acordo com a tabela TBPARLIMDOCS
SELECT @diasdevida = DiaVidDoc FROM TBPARLIMDOCS SELECT @totalregistro = QtdRegLimLoo FROM TBPARLIMDOCS SELECT @totaldedias = TotDiaExc FROM TBPARLIMDOCS SELECT @tempodeatraso = TemAtrLoo FROM TBPARLIMDOCS SELECT @limpalog = LimTLog FROM TBPARLIMDOCS SELECT @bulkLogged = BulkLogged FROM TBPARLIMDOCS SELECT @preserveRejecteds = PreserveRejecteds from TBPARLIMDOCS SELECT @InicioExec = GETDATE() SELECT @MaxProcessDataId = max(processdataid) FROM TBPROCESSDATA
-- Insere a linha de log para a execução
INSERT INTO TBLOGLIMPDOCS (HorIniLim,DiaVidDoc,TotDiaExc,BulkLogged,LimTLog,PreserveRejecteds,TotExcTbLogFilterDocument,TotExcTbProcess,TotExcTbProcessData,TotLoopDocInvalidos,TotExcTbEvent,TotExcTbProcessLots,TotExcTbLots,TotExcTbLogDocumentPrinted, TotExcTbImpression,TotExcTbRelatedDocument,TotExcTbLogDocMessage,TotExcTBENTRYDOCUMENT,TotExcTbLogDocument,TotExcTbLogDocumentStatus,TotLoopDocumentos) values (GETDATE(),@diasdevida,@totaldedias,@bulkLogged,@limpalog,@preserveRejecteds,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
-- Captura o id da execução na tabela TBPARLIMDOCS
SELECT @IdLimDocs = (SELECT TOP 1 IdLimpDocs FROM TBLOGLIMPDOCS ORDER BY 1 DESC)
-- Elimina logs da última execução
TRUNCATE TABLE TBLOGMSGS
-- Altera Forma de log de banco
IF @bulkLogged = 1 ALTER DATABASE [<BANCO_DE_DADOS>] SET RECOVERY BULK_LOGGED WITH NO_WAIT IF @bulkLogged = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Alterou o Recovery Model para BULK_LOGGED' EXEC ESCREVE_LOG @IdLimDocs, 'Início da Limpeza'
SET @qtdloop = 0
-- Início da captura de limites de informações
IF(@preserveRejecteds = 1) BEGIN SELECT @minDocDate = MIN(DocumentDate) FROM (SELECT min(TL.DocumentDate) DocumentDate FROM TBLOGDOCUMENT TL INNER JOIN TBNDDJOB JOB ON TL.JOBID = JOB.ID WHERE (TL.AllowDelete = 1 or TL.Hidden = 1) and TL.LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) and EXISTS ( SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (9,10,11) ORDER BY ((case when KIND = 3 then 1 when KIND = 43 then 2 when KIND = 99 then 3 when KIND = 45 then 4 when KIND = 48 then 5 when KIND = 42 then 6 when KIND = 47 then 7 when KIND = 46 then 8 when KIND = 110 then 9 when KIND = 44 then 10 when KIND = 74 then 11 when KIND = 51 then 12 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (3,43,99,45,48,42,47,46,74,51,71) UNION SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (0,1,2,3,4,5,7,8) ORDER BY ((case when KIND = 1 then 1 when KIND = 62 then 2 when KIND = 99 then 3 when KIND = 100 then 4 when KIND = 33 then 5 when KIND = 32 then 6 when KIND = 7 then 7 when KIND = 0 then 8 when KIND = 3 then 9 when KIND = 36 then 10 when KIND = 39 and TL.DOCUMENTSTATUS = 1 then 11 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 38 and kind <> 40 and TL.DOCUMENTSTATUS = 1 then 12 when KIND = 96 then 13 when KIND = 31 then 14 when KIND = 37 then 15 when KIND = 89 then 16 when KIND = 88 then 17 when (KIND = 19 or KIND = 29 or KIND = 30 or KIND = 97 or KIND = 41) then 101 when (KIND = 24 or KIND = 98 or kind = 68) and 0 = 0 then 101 when (KIND = 108) and TL.DOCUMENTSTATUS = 0 then 18 when KIND = 38 and TL.DOCUMENTSTATUS = 0 then 19 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 39 and TL.DOCUMENTSTATUS = 0 then 20 when KIND = 27 or KIND = 108 then 21 when KIND = 2 then 22 when KIND = 26 then 23 when KIND = 9 then 24 when KIND = 40 then 25 when KIND = 23 then 26 when KIND = 8 then 27 when KIND = 74 then 28 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (0,1,62,99,100,33,32,7,3,36,39,96,31,37,89,88,19,29,30,97,41,24,98,68,108,38,27,26,9,40,23,8,74,71) ) union SELECT min(DocumentDate) from tblogdocument where logdocid in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida))) b END ELSE BEGIN SELECT @minDocDate = MIN(DocumentDate) FROM (SELECT min(DocumentDate) DocumentDate FROM TBLOGDOCUMENT WHERE (AllowDelete = 1 or Hidden = 1) and LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) union SELECT min(DocumentDate) from tblogdocument where logdocid in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida))) b END
EXEC ESCREVE_LOG @IdLimDocs, 'Verificado total de registros para limpezas'
SELECT @minLotDate = min(LotDate)FROM TBLOT
EXEC ESCREVE_LOG @IdLimDocs, 'Verificado total de lotes para limpezas'
-- INICIO DA EXCLUSAO DE DOCUMENTOS EXPIRADOS
SET @qtdloop = 0
UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Inicio da limpeza de documentos expirados' where IdLimpDocs = @IdLimDocs SET @totalLimpezaLoop = @totalregistro
if @preserveRejecteds = 1 BEGIN SELECT @CountLoop = count(LogDocId) FROM ( select TL.LogDocId FROM TbLogDocument TL (NOLOCK) INNER JOIN TBNDDJOB JOB ON TL.JOBID = JOB.ID WHERE (TL.AllowDelete = 1 or TL.Hidden = 1) and TL.DocumentDate < @InicioExec - @diasdevida and TL.DocumentDate between @minDocDate and @minDocDate+@totaldedias and TL.LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) and EXISTS ( SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (9,10,11) ORDER BY ((case when KIND = 3 then 1 when KIND = 43 then 2 when KIND = 99 then 3 when KIND = 45 then 4 when KIND = 48 then 5 when KIND = 42 then 6 when KIND = 47 then 7 when KIND = 46 then 8 when KIND = 110 then 9 when KIND = 44 then 10 when KIND = 74 then 11 when KIND = 51 then 12 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (3,43,99,45,48,42,47,46,74,51,71) UNION SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (0,1,2,3,4,5,7,8) ORDER BY ((case when KIND = 1 then 1 when KIND = 62 then 2 when KIND = 99 then 3 when KIND = 100 then 4 when KIND = 33 then 5 when KIND = 32 then 6 when KIND = 7 then 7 when KIND = 0 then 8 when KIND = 3 then 9 when KIND = 36 then 10 when KIND = 39 and TL.DOCUMENTSTATUS = 1 then 11 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 38 and kind <> 40 and TL.DOCUMENTSTATUS = 1 then 12 when KIND = 96 then 13 when KIND = 31 then 14 when KIND = 37 then 15 when KIND = 89 then 16 when KIND = 88 then 17 when (KIND = 19 or KIND = 29 or KIND = 30 or KIND = 97 or KIND = 41) then 101 when (KIND = 24 or KIND = 98 or kind = 68) and 0 = 0 then 101 when (KIND = 108) and TL.DOCUMENTSTATUS = 0 then 18 when KIND = 38 and TL.DOCUMENTSTATUS = 0 then 19 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 39 and TL.DOCUMENTSTATUS = 0 then 20 when KIND = 27 or KIND = 108 then 21 when KIND = 2 then 22 when KIND = 26 then 23 when KIND = 9 then 24 when KIND = 40 then 25 when KIND = 23 then 26 when KIND = 8 then 27 when KIND = 74 then 28 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (0,1,62,99,100,33,32,7,3,36,39,96,31,37,89,88,19,29,30,97,41,24,98,68,108,38,27,26,9,40,23,8,74,71) ) UNION SELECT DISTINCT(LogDocId) LogDocId FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida) ) b END ELSE BEGIN SELECT @CountLoop = count(LogDocId) FROM ( SELECT LogDocId FROM TbLogDocument (NOLOCK) WHERE (AllowDelete = 1 or Hidden = 1) and DocumentDate < @InicioExec - @diasdevida and DocumentDate between @minDocDate and @minDocDate+@totaldedias and LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) UNION SELECT DISTINCT(LogDocId) LogDocId FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida) ) b END
SET @MsgLog = 'Total de documentos expirados encontrados: ' + CAST((@CountLoop) as varchar(18))
EXEC ESCREVE_LOG @IdLimDocs, @MsgLog
WHILE @CountLoop > 0 BEGIN -- Limpa tabelas para seleção dos registros
EXEC ESCREVE_LOG @IdLimDocs, 'Iniciado laço de limpeza da TbLogDocument'
TRUNCATE TABLE tmpLogsDocIds TRUNCATE TABLE tmpProcessIDs TRUNCATE TABLE tmpLotIDs
INSERT INTO TBLOGMSGS (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), 'Capturados valores mínimos') SET @qtdloop = @qtdloop + 1
IF @limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @IdLimDocs IF @limpalog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Limpou logs de transação e vai inserir registros de tmpLogsDocIds'
UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Carga dos documentos expirados' where IdLimpDocs = @IdLimDocs
IF @preserveRejecteds = 1 BEGIN SELECT @sql = 'INSERT tmpLogsDocIds SELECT DISTINCT TOP ' + CONVERT(varchar(10),@totalregistro) + ' LOGDOCID FROM ( SELECT TL.LogDocId FROM TbLogDocument TL (NOLOCK) INNER JOIN TBNDDJOB JOB ON TL.JOBID = JOB.ID WHERE (TL.AllowDelete = 1 or TL.Hidden = 1) and TL.DocumentDate < convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' and TL.DocumentDate between convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) and convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) + ' + CONVERT(varchar(10),@totaldedias) + ' and TL.LogDocId NOT IN ( SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136)) ) and EXISTS ( SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (9,10,11) ORDER BY ((case when KIND = 3 then 1 when KIND = 43 then 2 when KIND = 99 then 3 when KIND = 45 then 4 when KIND = 48 then 5 when KIND = 42 then 6 when KIND = 47 then 7 when KIND = 46 then 8 when KIND = 110 then 9 when KIND = 44 then 10 when KIND = 74 then 11 when KIND = 51 then 12 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (3,43,99,45,48,42,47,46,74,51,71) UNION SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (0,1,2,3,4,5,7,8) ORDER BY ((case when KIND = 1 then 1 when KIND = 62 then 2 when KIND = 99 then 3 when KIND = 100 then 4 when KIND = 33 then 5 when KIND = 32 then 6 when KIND = 7 then 7 when KIND = 0 then 8 when KIND = 3 then 9 when KIND = 36 then 10 when KIND = 39 and TL.DOCUMENTSTATUS = 1 then 11 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 38 and kind <> 40 and TL.DOCUMENTSTATUS = 1 then 12 when KIND = 96 then 13 when KIND = 31 then 14 when KIND = 37 then 15 when KIND = 89 then 16 when KIND = 88 then 17 when (KIND = 19 or KIND = 29 or KIND = 30 or KIND = 97 or KIND = 41) then 101 when (KIND = 24 or KIND = 98 or kind = 68) and 0 = 0 then 101 when (KIND = 108) and TL.DOCUMENTSTATUS = 0 then 18 when KIND = 38 and TL.DOCUMENTSTATUS = 0 then 19 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 39 and TL.DOCUMENTSTATUS = 0 then 20 when KIND = 27 or KIND = 108 then 21 when KIND = 2 then 22 when KIND = 26 then 23 when KIND = 9 then 24 when KIND = 40 then 25 when KIND = 23 then 26 when KIND = 8 then 27 when KIND = 74 then 28 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (0,1,62,99,100,33,32,7,3,36,39,96,31,37,89,88,19,29,30,97,41,24,98,68,108,38,27,26,9,40,23,8,74,71) ) UNION SELECT DISTINCT(LogDocId) FROM TbLogDocumentstatus (NOLOCK) WHERE Kind = 7 and DateStatus <= convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' and DateStatus between convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) and convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) + ' + CONVERT(varchar(10),@totaldedias) + ' ) TEMP ORDER BY LOGDOCID' END ELSE BEGIN SELECT @sql = 'INSERT tmpLogsDocIds SELECT DISTINCT TOP ' + CONVERT(varchar(10),@totalregistro) + ' LOGDOCID FROM ( SELECT LogDocId FROM TbLogDocument (NOLOCK) WHERE (AllowDelete = 1 or Hidden = 1) and DocumentDate < convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' and DocumentDate between convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) and convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) + ' + CONVERT(varchar(10),@totaldedias) + ' and LogDocId NOT IN ( SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136)) ) UNION SELECT DISTINCT(LogDocId) FROM TbLogDocumentstatus (NOLOCK) WHERE Kind = 7 and DateStatus <= convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' and DateStatus between convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) and convert(datetime, ''' + CONVERT(varchar(23),@minDocDate, 120) + ''', 120) + ' + CONVERT(varchar(10),@totaldedias) + ' ) TEMP ORDER BY LOGDOCID' END
EXECUTE (@sql)
SELECT @totalLimpezaLoop = count(*) from tmpLogsDocIds SET @MsgLog = 'Inseriu registros na tmpLogsDocIds -> ' + CAST((@totalLimpezaLoop) as varchar(18)) EXEC ESCREVE_LOG @IdLimDocs, @MsgLog
IF @totalLimpezaLoop = 0 BEGIN EXEC ESCREVE_LOG @IdLimDocs, 'Encerrada a limpeza de documentos normais. Não existem documentos expirados no período de tempo especificado' BREAK END
-- Busca todos os ProcessIDs que estão relacionados com os LogsDocIds que podem ser removidos
INSERT TmpProcessIDs SELECT ProcessID, RegistryProcessID FROM TbProcess (NOLOCK) WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds)
EXEC ESCREVE_LOG @IdLimDocs, 'Inseriu registros na ProcessIDs'
/* EVENTOS */
-- Remove os registros de eventos que estão vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbEvent WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbEvent = TotExcTbEvent + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbEvent'
INSERT tmpLotIDs SELECT DISTINCT(LotId) FROM TbProcessLots WHERE ProcessID IN (SELECT PROCESSID FROM TmpProcessIDs) EXEC ESCREVE_LOG @IdLimDocs, 'Inseriu Registros na LotsIDs'
-- Remove os processos em lotes de eventos que estão vinculados aos ProcessIDs que serão removidos
DELETE FROM TbProcessLots WHERE LotId IN (SELECT LotId FROM TmpLotIDs) UPDATE TBLOGLIMPDOCS SET TotExcTbProcessLots = TotExcTbProcessLots + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da tbProcessLots'
-- Remove os lotes de eventos que estão vinculados aos ProcessIDs que serão removidos
DELETE FROM TbLots WHERE LotId IN (SELECT LotId FROM TmpLotIDs) UPDATE TBLOGLIMPDOCS SET TotExcTbLots = TotExcTbLots + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da tbLots'
/* PROCESSOS */
-- Remove os registros de processos que estão vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbProcess WITH (ROWLOCK) WHERE ProcessID IN (SELECT DISTINCT ProcessID FROM TmpProcessIDs UNION SELECT DISTINCT RegistryProcessID FROM TmpProcessIDs where RegistryProcessID IS NOT NULL) UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcess'
-- Remove os registros da tabela de automação de processos que não possuem referência na tabela de processos
DELETE FROM TbAutomate WITH (ROWLOCK) WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess (NOLOCK) WHERE PROCESSDATAID > 0) AND PROCESSDATAID < @maxProcessDataId EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbAutomate'
-- Remove os registros de documento original/convertidos que não possuem referência na tabela de processos
DELETE FROM TbProcessData WITH (ROWLOCK) WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess (NOLOCK) WHERE PROCESSDATAID > 0 UNION SELECT DISTINCT ProcessDataId FROM TBEvent(NOLOCK) WHERE PROCESSDATAID > 0) AND PROCESSDATAID < @maxProcessDataId UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcessData'
/* REIMPRESSÃO PELO E-MONITOR */
-- Remove os registros de reimpressão vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbLogDocumentPrinted WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentPrinted = TotExcTbLogDocumentPrinted + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogDocumentPrinted'
/* DOCUMENTOS RELACIONADOS */
-- Remove os registros de documentos relacionados através da automação vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbRelatedDocument where LogDocID IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbRelatedDocument = TotExcTbRelatedDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbRelatedDocument'
/* MENSAGENS */
-- Remove os registros de mensagens vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbLogDocMessage WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocMessage = TotExcTbLogDocMessage + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogDocMessage'
/* DOCUMENTOS DE ENTRADA */
-- Remove os registros de sumário de documentos de entrada vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TBENTRYDOCUMENT WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTBENTRYDOCUMENT = TotExcTBENTRYDOCUMENT + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TBENTRYDOCUMENT'
/* STATUS */
-- Remove os registros de status vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbLogDocumentStatus WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentStatus = TotExcTbLogDocumentStatus + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogDocumentStatus'
/* LOGS DE AÇÕES DO MONITOR */
-- Remove os registros de log de ações do e-monitor vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TbLogDocumentAction WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentAction = TotExcTbLogDocumentAction + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogDocumentAction'
/* SENDER CONSULT */
-- Remove os registros do sender consult vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TBSENDERCONSULT WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbSenderConsult'
/* DOWNLOAD */
-- Remove os registros de download vinculados aos tmpLogsDocIds que serão removidos
DELETE FROM TBDOWNLOADITEMLOT WHERE DOWNLOADITEMID IN (SELECT DOWNLOADITEMID FROM TBDOWNLOADITEM WHERE LOGDOCID IN (SELECT LogDocId FROM tmpLogsDocIds)) EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbDownloadItemLot'
DELETE FROM TBDOWNLOADITEM WHERE LOGDOCID IN (SELECT LogDocId FROM tmpLogsDocIds) EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbDownloadItem'
DELETE FROM TBDOWNLOADLOT WHERE DOWNLOADLOTID NOT IN (SELECT DOWNLOADLOTID FROM TBDOWNLOADITEMLOT) EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TBDOWNLOADLOT'
/* LOGS */
-- Remove os registros de log de documento que estão com a exclusão permitida
DELETE FROM TbLogDocument WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocument = TotExcTbLogDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogDocument'
/* LOTES */
-- Remove os lotes que estão com data de criação 30 dias mais antigos do que a data de retenção
DELETE FROM TbLot WHERE LotDate < getDate() - @diasdevida and LotDate between @minLotDate and @minLotDate + @totaldedias UPDATE TBLOGLIMPDOCS SET TotExcTbLots = TotExcTbLots + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLot'
WAITFOR DELAY @tempodeatraso
SELECT @CountLoop = 0;
IF @preserveRejecteds = 1 BEGIN SELECT @CountLoop = count(LogDocId) FROM (select TL.LogDocId FROM TbLogDocument TL (NOLOCK) INNER JOIN TBNDDJOB JOB ON TL.JOBID = JOB.ID WHERE (TL.AllowDelete = 1 or TL.Hidden = 1) and TL.DocumentDate < @InicioExec - @diasdevida and TL.DocumentDate between @minDocDate and @minDocDate+@totaldedias and TL.LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) and EXISTS ( SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (9,10,11) ORDER BY ((case when KIND = 3 then 1 when KIND = 43 then 2 when KIND = 99 then 3 when KIND = 45 then 4 when KIND = 48 then 5 when KIND = 42 then 6 when KIND = 47 then 7 when KIND = 46 then 8 when KIND = 110 then 9 when KIND = 44 then 10 when KIND = 74 then 11 when KIND = 51 then 12 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (3,43,99,45,48,42,47,46,74,51,71) UNION SELECT 1 FROM (SELECT TOP 1 (KIND) FROM TBLOGDOCUMENTSTATUS WHERE LOGDOCID = TL.LOGDOCID AND JOB.KIND IN (0,1,2,3,4,5,7,8) ORDER BY ((case when KIND = 1 then 1 when KIND = 62 then 2 when KIND = 99 then 3 when KIND = 100 then 4 when KIND = 33 then 5 when KIND = 32 then 6 when KIND = 7 then 7 when KIND = 0 then 8 when KIND = 3 then 9 when KIND = 36 then 10 when KIND = 39 and TL.DOCUMENTSTATUS = 1 then 11 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 38 and kind <> 40 and TL.DOCUMENTSTATUS = 1 then 12 when KIND = 96 then 13 when KIND = 31 then 14 when KIND = 37 then 15 when KIND = 89 then 16 when KIND = 88 then 17 when (KIND = 19 or KIND = 29 or KIND = 30 or KIND = 97 or KIND = 41) then 101 when (KIND = 24 or KIND = 98 or kind = 68) and 0 = 0 then 101 when (KIND = 108) and TL.DOCUMENTSTATUS = 0 then 18 when KIND = 38 and TL.DOCUMENTSTATUS = 0 then 19 when KIND <> 0 and KIND <> 1 and KIND <> 3 and KIND <> 7 and kind <> 39 and TL.DOCUMENTSTATUS = 0 then 20 when KIND = 27 or KIND = 108 then 21 when KIND = 2 then 22 when KIND = 26 then 23 when KIND = 9 then 24 when KIND = 40 then 25 when KIND = 23 then 26 when KIND = 8 then 27 when KIND = 74 then 28 when KIND = 71 then 99 else 100 end) )) KIND WHERE KIND IN (0,1,62,99,100,33,32,7,3,36,39,96,31,37,89,88,19,29,30,97,41,24,98,68,108,38,27,26,9,40,23,8,74,71) ) UNION SELECT DISTINCT(LogDocId) LogDocId FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida)) b END ELSE BEGIN SELECT @CountLoop = count(LogDocId) FROM (select LogDocId FROM TbLogDocument (NOLOCK) WHERE (AllowDelete = 1 or Hidden = 1) and DocumentDate < @InicioExec - @diasdevida and DocumentDate between @minDocDate and @minDocDate+@totaldedias and LogDocId NOT IN (SELECT DISTINCT(LogDocId) FROM TbEvent (NOLOCK) WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) UNION SELECT DISTINCT(LogDocId) LogDocId FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida)) b END END
TRUNCATE TABLE tmpLogsDocIds TRUNCATE TABLE tmpProcessIDs TRUNCATE TABLE tmpLotIDs
UPDATE TBLOGLIMPDOCS SET TotLoopDocumentos = @qtdloop where IdLimpDocs = @IdLimDocs
-- FINAL DA EXCLUSAO DE DOCUMENTOS EXPIRADOS
-- INICIO DA EXCLUSAO DE ENTRADAS INVALIDAS
EXEC ESCREVE_LOG @IdLimDocs, 'Iniciando limpeza de entradas inválidas' UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando entradas inválidas' where IdLimpDocs = @IdLimDocs WHILE EXISTS (SELECT TOP 1 INVALIDENTRYDOCID FROM TbInvalidEntryDocument WHERE ENTRYDATE <= @InicioExec - @diasdevida) BEGIN SET @qtdloop = @qtdloop + 1 IF @limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @IdLimDocs UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Removendo entradas inválidas' where IdLimpDocs = @IdLimDocs
-- Remove registros de entradas inválidas
SELECT @sql = 'DELETE TOP (' + CONVERT(varchar(10),@totalregistro) + ') FROM TbInvalidEntryDocument WHERE ENTRYDATE <= convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) execute (@sql)
UPDATE TBLOGLIMPDOCS SET TotExcTbLogFilterDocument = TotExcTbLogFilterDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbInvalidEntryDocument'
WAITFOR DELAY @tempodeatraso END
-- FINAL DA EXCLUSAO DE ENTRADAS INVALIDAS
-- INICIO DA EXCLUSAO DE DOCUMENTOS INVALIDOS
EXEC ESCREVE_LOG @IdLimDocs, 'Iniciando limpeza de documentos inválidos' UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando documentos inválidos' where IdLimpDocs = @IdLimDocs WHILE EXISTS (SELECT TOP 1 PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida) BEGIN TRUNCATE TABLE tmpDocInvalidsProcessIDs SET @qtdloop = @qtdloop + 1 IF @limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @IdLimDocs UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Removendo documentos inválidos' where IdLimpDocs = @IdLimDocs
SELECT @sql = 'INSERT tmpDocInvalidsProcessIDs SELECT TOP ' + CONVERT(varchar(10),@totalregistro) + ' ProcessID, ProcessDataID FROM TbProcess (NOLOCK) WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ')' execute (@sql)
SET @MsgLog = 'Carregou ' + CAST(@@ROWCOUNT as varchar(18))+ ' tmpDocInvalidsProcessIDs para remoção' EXEC ESCREVE_LOG @IdLimDocs, @MsgLog
SELECT @totalLimpezaLoop = count(*) from tmpDocInvalidsProcessIDs
IF @totalLimpezaLoop = 0 BEGIN EXEC ESCREVE_LOG @IdLimDocs, 'Terminou limpeza devido problemas de busca dos documntos inválidos' BREAK END
-- Remove registros de documentos inválidos DELETE FROM TbLogFilterDocument WHERE ProcessID IN (SELECT ProcessId FROM tmpDocInvalidsProcessIDs) UPDATE TBLOGLIMPDOCS SET TotExcTbLogFilterDocument = TotExcTbLogFilterDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogFilterDocument'
-- Remove registros de processos de documentos inválidos DELETE FROM TbProcess WHERE ProcessID IN (SELECT ProcessId FROM tmpDocInvalidsProcessIDs) UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcess'
-- Remove registros de documentos orignais inválidos DELETE FROM TbProcessData WITH (ROWLOCK) WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess (NOLOCK) WHERE PROCESSDATAID > 0 UNION SELECT DISTINCT ProcessDataId FROM TBEvent(NOLOCK) WHERE PROCESSDATAID > 0) AND PROCESSDATAID < @maxProcessDataId UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcessData'
WAITFOR DELAY @tempodeatraso END
TRUNCATE TABLE tmpDocInvalidsProcessIDs
UPDATE TBLOGLIMPDOCS SET TotLoopDocInvalidos = @qtdloop where IdLimpDocs = @IdLimDocs
-- FINAL DA EXCLUSAO DE DOCUMENTOS INVALIDOS
-- INICIO DA EXCLUSÃO DE PROCESSOS ORFÃOS
-- Remove registros da tbprocess que não estão vinculados a logdocid
EXEC ESCREVE_LOG @IdLimDocs, 'Iniciando limpeza de processos orfãos'
UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando processos Orfãos' where IdLimpDocs = @IdLimDocs
SELECT @minProcessDate = min(StartDate) FROM TBPROCESS WHERE LogDocID IS NULL AND PROCESSSTATUS IN (1,2,3) and STARTDATE < @InicioExec - @diasdevida
WHILE EXISTS(SELECT TOP (@totalregistro) ProcessID, RegistryProcessID, ProcessDataID FROM TbProcess (NOLOCK) WHERE LogDocID IS NULL AND STARTDATE <= @minProcessDate + @totaldedias and PROCESSSTATUS IN (1,2,3) and STARTDATE < @InicioExec - @diasdevida) BEGIN SELECT @sql = 'INSERT tmpProcessIDs SELECT TOP ' + CONVERT(varchar(10),@totalregistro) + ' ProcessID, RegistryProcessID FROM TbProcess (NOLOCK) WHERE LogDocID IS NULL AND STARTDATE < convert(datetime, ''' + CONVERT(varchar(23),@minProcessDate, 121) + ''', 120) + ' + CONVERT(varchar(10),@totaldedias) + ' and PROCESSSTATUS IN (1,2,3) and STARTDATE < convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) EXECUTE(@sql) SELECT @totalLimpezaLoop = count(*) from tmpProcessIds IF @totalLimpezaLoop = 0 BEGIN EXEC ESCREVE_LOG @IdLimDocs, 'Terminou limpeza de processos orfãos' BREAK END
SET @MsgLog = 'Excluindo processos orfãos -> ' + CAST((@totalLimpezaLoop) as varchar(18)) EXEC ESCREVE_LOG @IdLimDocs, @MsgLog
DELETE FROM TbLogFilterDocument WHERE ProcessID IN (SELECT PROCESSID FROM TmpProcessIds) UPDATE TBLOGLIMPDOCS SET TotExcTbLogFilterDocument = TotExcTbLogFilterDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbLogFilterDocument'
DELETE FROM TBPROCESS WITH (ROWLOCK) WHERE ProcessId IN (SELECT PROCESSID FROM TmpProcessIds) UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcess'
-- remove registros da tbprocessdata que foram esxcluidos da tbprocess DELETE FROM TbProcessData WITH (ROWLOCK) WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess (NOLOCK) WHERE PROCESSDATAID > 0 UNION SELECT DISTINCT ProcessDataId FROM TBEvent(NOLOCK) WHERE PROCESSDATAID > 0) AND PROCESSDATAID < @maxProcessDataId UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da TbProcessData' TRUNCATE TABLE TmpProcessIds
WAITFOR DELAY @tempodeatraso END
-- FINAL DA EXCLUSÃO DE PROCESSOS ORFÃOS
-- LIMPEZA DAS TABELAS DO PRODUTO WEB SERVICES
UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando tabelas do produto Web Services' where IdLimpDocs = @IdLimDocs
SET @countWsRegs = 1
WHILE(@countWsRegs > 0) BEGIN -- Remove os registros das tabelas do produto Web Services
SET @countWsRegs = 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEDOCUMENTCONTROL]')) BEGIN DELETE TOP (@totalregistro) FROM TBWEBSERVICEDOCUMENTCONTROL WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEDOCUMENTCONTROL) + @totaldedias SELECT @countWsRegs += COUNT(docNumber) FROM TBWEBSERVICEDOCUMENTCONTROL WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEDOCUMENTCONTROL) + @totaldedias END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEDOCUMENT]')) BEGIN DELETE FROM TBWEBSERVICEDOCUMENT WHERE protocolId IN (SELECT TOP (@totalregistro) protocolId FROM TBWEBSERVICEDOCUMENT WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEDOCUMENT) + @totaldedias) SELECT @countWsRegs += COUNT(protocolId) FROM TBWEBSERVICEDOCUMENT WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEDOCUMENT) + @totaldedias END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEINPUT]')) BEGIN DELETE FROM TBWEBSERVICEINPUT WHERE serviceInputId IN (SELECT TOP (@totalregistro) serviceInputId FROM TBWEBSERVICEINPUT WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEINPUT) + @totaldedias) SELECT @countWsRegs += COUNT(serviceInputId) FROM TBWEBSERVICEINPUT WHERE INSERTDATE < GETDATE() - @diasdevida and INSERTDATE < (SELECT min(INSERTDATE) FROM TBWEBSERVICEINPUT) + @totaldedias END
WAITFOR DELAY @tempodeatraso END
-- FINAL DA LIMPEZA DAS TABELAS DO PRODUTO WEB SERVICES
UPDATE TBLOGLIMPDOCS SET HorFimLim = GETDATE(), StatusAtual = 'Limpeza Finalizada' where IdLimpDocs = @IdLimDocs EXEC ESCREVE_LOG @IdLimDocs, 'Finalizou Limpeza'
-- VOLTANDO BACKUP LOG
IF @bulkLogged = 1 ALTER DATABASE [<BANCO_DE_DADOS>] SET RECOVERY FULL WITH NO_WAIT IF @bulkLogged = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Alterou o Recovery Model para FULL' END TRY
BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT;
SELECT @ErrorMessage = 'Ocorreu um erro na rotina LIMPA_REGISTROS_EXPIRADOS ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH LOG; END CATCH |