MSSQL 2008

Anterior
Próximo
Feedback
Imprimir

MSSQL 2008

DLL Limpeza SQL 2008

 

-- 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