Aconselhável acompanhamento de um especialista em banco de dados para esse procedimento. |
-- Mudar configuração do banco LEMBRAR DE TROCAR O VALOR <BANCO_DE_DADOS> PELO NOME DA BASE DE DADOS
-- Criando indice 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 estatistica para melhorar o desempenho da limpeza
CREATE STATISTICS [_dt_stat_tbprocess] ON [dbo].[TBPROCESS]([LOGDOCID], [PROCESSID])
GO
-- Habilitando o Ad Hoc Distributed Queries
EXEC dbo.sp_executesql @statement = N'sp_configure ''show advanced options'', 1
RECONFIGURE;'
EXEC dbo.sp_executesql @statement = N'sp_configure ''Ad Hoc Distributed Queries'', 1
RECONFIGURE;'
-- Criar tabelas temporarias
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
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
EXEC dbo.sp_executesql @statement = N'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'
-- Criar procedimento de limpeza monitoramento de job
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
EXEC dbo.sp_executesql @statement = N'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
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
EXEC dbo.sp_executesql @statement = N'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 diretorio 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 conclusao
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 execucao 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 execucao do job''+@NomJobTLog WHERE IdLimpDocs = @IdLimDocs
WAITFOR DELAY ''00:00:05''
EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;
END
END
END'
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
EXEC dbo.sp_executesql @statement = N'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(1000),
@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
-- carrega as variareis 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 execucao
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 execucao na tabela TBPARLIMDOCS
SELECT @IdLimDocs = (SELECT TOP 1 IdLimpDocs FROM TBLOGLIMPDOCS ORDER BY 1 DESC)
--Elimina logs da última execucao
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, ''Inicio da Limpeza''
SET @qtdloop = 0
-- inicio da captura de limites de informcõ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 selecao dos registros
EXEC ESCREVE_LOG @IdLimDocs, ''Iniciado laco de limpeza da TbLogDocument''
TRUNCATE TABLE tmpLogsDocIds
TRUNCATE TABLE tmpProcessIDs
TRUNCATE TABLE tmpLotIDs
INSERT INTO TBLOGMSGS (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), ''Capturados valores minimos'')
SET @qtdloop = @qtdloop + 1
IF @limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @IdLimDocs
IF @limpalog = 1 EXEC ESCREVE_LOG @IdLimDocs, ''Limpou logs de transacao 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. Nao existem documentos expirados no periodo de tempo especificado''
BREAK
END
-- busca todos os ProcessIDs que estao 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 estao vinculados aos tmpLogsDocIds que serao 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 estao vinculados aos ProcessIDs que serao 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 estao vinculados aos ProcessIDs que serao 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''
/*
CONCILIAÇAO
*/
-- remove as concilicões que estao com data de criacao 30 dias mais antigos do que a data de retencao
DELETE FROM TbConciliation WHERE ProcessID IN (SELECT ProcessID FROM TmpProcessIDs
UNION
SELECT processid FROM TBPROCESS
WHERE PROCESSID in (SELECT RegistryProcessID FROM TmpProcessIDs)) OR PROCESSID IS NULL
EXEC ESCREVE_LOG @IdLimDocs, ''Deletou da tbConciliation'';
/*
PROCESSOS
*/
-- remove os registros de processos que estao vinculados aos tmpLogsDocIds que serao 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 automacao de processos que nao possuem referência na tabela de processos
DELETE FROM TbAutomate WITH (ROWLOCK) WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess (NOLOCK)) AND PROCESSDATAID < @maxProcessDataId
EXEC ESCREVE_LOG @IdLimDocs, ''Deletou da TbAutomate''
-- remove os registros de documento original/convertidos que nao 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''
/*
REIMPRESSAO PELO E-MONITOR
*/
-- remove os registros de reimpressao vinculados aos tmpLogsDocIds que serao 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 atraves da automacao vinculados aos tmpLogsDocIds que serao 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 orfaos, caso apenas o primeiro documento tenha sido excluido 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 orfaos da TbRelatedDocument''
/*
MENSAGENS
*/
-- remove os registros de mensagens vinculados aos tmpLogsDocIds que serao 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 sumario de documentos de entrada vinculados aos tmpLogsDocIds que serao 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 serao 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''
/*
LOGDOCUMENTDETAIL
*/
-- remove os registros de status vinculados aos tmpLogsDocIds que serao removidos
DELETE FROM TBLOGDOCUMENTDETAIL WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds)
/*
LOGS DE AÇÕES DO MONITOR
*/
-- remove os registros de log de acões do e-monitor vinculados aos tmpLogsDocIds que serao 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 serao removidos
DELETE FROM TBSENDERCONSULT WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds)
EXEC ESCREVE_LOG @IdLimDocs, ''Deletou da TbSenderConsult''
/*
LOGS
*/
-- remove os registros de log de documento que estao com a exclusao 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 estao com data de criacao 30 dias mais antigos do que a data de retencao
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 invalidos''
UPDATE TBLOGLIMPDOCS SET StatusAtual = ''Limpando documentos invalidos'' 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 invalidos'' 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 remocao''
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 invalidos''
BREAK
END
-- remove registros de documentos invalidos
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 invalidos
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 invalidos
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 EXCLUSAO DE PROCESSOS ORFAOS
-- remove registros da tbprocess que nao estao vinculados a logdocid
EXEC ESCREVE_LOG @IdLimDocs, ''Iniciando limpeza de processos orfaos''
UPDATE TBLOGLIMPDOCS SET StatusAtual = ''Limpando processos Orfaos'' 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 orfaos''
BREAK
END
SET @MsgLog = ''Excluindo processos orfaos -> '' + 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 EXCLUSAO DE PROCESSOS ORFAOS
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'
DECLARE @jobIdDelete binary(16)
SELECT @jobIdDelete = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Limpeza e-Forms - <BANCO_DE_DADOS>')
IF (@jobIdDelete IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobIdDelete
END
/****** Object: Job [Rotina de Limpeza e-Forms] Script Date: 09/01/2014 08:19:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/01/2014 08:19:38 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Limpeza e-Forms - <BANCO_DE_DADOS>',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Rotina responsavel pela limpeza das tabelas de processamento do e-Forms',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
/****** Object: Step [Limpeza de Particao] Script Date: 09/01/2014 08:19:38 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Limpeza das tabelas de processamento do eForms',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec LIMPA_REGISTROS_EXPIRADOS;',
@database_name=N'<BANCO_DE_DADOS>',
@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Execucao diaria',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140107,
@active_end_date=99991231,
@active_start_time=10000,
@active_end_time=235959,
@schedule_uid=N'7ab21128-8ca5-49b2-b25d-f6b5f9983a16'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
COMMIT TRANSACTION
goto EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
INSERT INTO TBPARLIMDOCS VALUES(365,1000, '00:00:01', 1,0,0,'PROC','C:\BKP_LOG', '', 1);