-- 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 (será que precisa)
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,
[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
) 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 limpeza do transaction log, caso [TBPARLIMDOCS].[LimTLog] = 1
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
-- 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]
GO
CREATE procedure [dbo].[sp_MonitorProcessingJob](@status int OUTPUT)
AS
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]
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 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
ELSE -- executa job destinado para backup do transaction log, caso o status = 1 (rodando) a procedure aguarda a conclusão
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
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(1500),
@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
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 @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,TotExcTbLogFilterDocument,TotExcTbProcess,TotExcTbProcessData,TotLoopDocInvalidos,TotExcTbEvent,TotExcTbProcessLots,TotExcTbLots,TotExcTbLogDocumentPrinted,
TotExcTbImpression,TotExcTbRelatedDocument,TotExcTbLogDocMessage,TotExcTBENTRYDOCUMENT,TotExcTbLogDocument,TotExcTbLogDocumentStatus,TotLoopDocumentos)
values (GETDATE(),@diasdevida,@totaldedias,@bulkLogged,@limpalog,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
SELECT @minDocDate = min(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))) or
LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida))
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
SELECT @CountLoop = count(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))) or
LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida))
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
SELECT @sql = 'INSERT tmpLogsDocIds SELECT TOP ' + CONVERT(varchar(10),@totalregistro) + ' 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))) or LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ')) 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) + ' order by 1'
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 ProcessID FROM TmpProcessIDs UNION SELECT processid FROM TBPROCESS WHERE PROCESSID in (SELECT RegistryProcessID FROM tmpProcessIDs))
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'
-- remove os registros de documentos relacionados que podem ter ficado órfãos, caso apenas o primeiro documento tenha sido excluído no comando anterior
DELETE FROM TbRelatedDocument WHERE ParentId NOT IN (SELECT Id FROM TbRelatedDocument (NOLOCK))
UPDATE TBLOGLIMPDOCS SET TotExcTbRelatedDocument = TotExcTbRelatedDocument + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs
EXEC ESCREVE_LOG @IdLimDocs, 'Deletou orfãos 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;
SELECT @CountLoop = count(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))) or
LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= @InicioExec - @diasdevida))
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 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() - 7 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() - 7 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() - 7 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, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
) WITH LOG;
END CATCH
|