MSSQL 2012/2014

DLL Limpeza SQL 2012/2014

-- Mudar configuração do banco

USE [<BANCO_DE_DADOS>]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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

-- Criando estatística para melhorar o desempenho da limpeza

CREATE STATISTICS [_dt_stat_tbprocess] ON [dbo].[TBPROCESS]([LOGDOCID], [PROCESSID])

-- Habilitando o Ad Hoc Distributed Queries

sp_configure 'show advanced options', 1

RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE;

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

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]

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]

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]

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

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]

ALTER TABLE [dbo].[TBPARLIMDOCS] ADD CONSTRAINT [DF_TBPARLIMDOCS_MsgLog] DEFAULT ((0)) FOR [MsgLog]

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

-- Procedimento de limpeza do transaction log, caso [TBPARLIMDOCS].[LimTLog] = 1

CREATE PROCEDURE [dbo].[ESCREVE_LOG] @IdLimDocs int, @TxtLog varchar(500)

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

-- Criar procedimento de limpeza monitoramento de job

/****** Object: StoredProcedure [dbo].[sp_MonitorProcessingJob] Script Date: 09/23/2012 22:26:00 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_MonitorProcessingJob]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_MonitorProcessingJob]

CREATE procedure [dbo].[sp_MonitorProcessingJob](@status int OUTPUT)

BEGIN

BEGIN TRY

select @status = current_execution_status

from openrowset ('sqlncli','Server=(local);Trusted_Connection=yes',

'EXEC msdb.dbo.sp_help_job @job_name = ''Backup TLog.Subplan_1'', @job_aspect = ''JOB''

WITH RESULT SETS((job_id uniqueidentifier, originating_server nvarchar(30), name sysname,

enabled tinyint,description nvarchar(512),start_step_id int,category sysname,owner sysname,

notify_level_eventlog int, notify_level_email int,notify_level_netsend int,notify_level_page int,

notify_email_operator sysname,notify_netsend_operator sysname, notify_page_operator sysname,

delete_level int,date_created datetime,date_modified datetime,version_number int,last_run_date int,

last_run_time int,last_run_outcome int,next_run_date int,next_run_time int,next_run_schedule_id int,

current_execution_status int,current_execution_step sysname,current_retry_attempt int,has_step int,

has_schedule int,has_target int,type int))');

END TRY

BEGIN CATCH

SET @status = 4

END CATCH

END;

-- Criar procedimento de limpeza do transaction log, caso [TBPARLIMDOCS].[LimTLog] = 1

/****** 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]

-- Procedimento de limpeza do transaction log, caso [TBPARLIMDOCS].[LimTLog] = 1

CREATE PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG] @IdLimDocs int

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 WHERE IdLimpDocs = @IdLimDocs

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

EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

-- 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 WHERE IdLimpDocs = @IdLimDocs

WAITFOR DELAY '00:00:05'

EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

END

-- Executa o job informado

EXEC msdb.dbo.sp_start_job @NomJobTLog;

-- Aguarda o job encerrar para voltar para a procedure de limpeza

EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

WHILE @RunStatus = 1

BEGIN

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Aguardando execução do job'+@NomJobTLog WHERE IdLimpDocs = @IdLimDocs

WAITFOR DELAY '00:00:05'

EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

END

/****** 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]

CREATE PROCEDURE [dbo].[LIMPA_REGISTROS_EXPIRADOS]

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 informçõ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)))

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 = 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 = 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) + '

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

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

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

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