|
IMPORTANTE: •Aconselhável acompanhamento de um especialista em banco de dados para esse procedimento. |
-- Mudar configuracao 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'
-- 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
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
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 SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET (''SQLOLEDB'',''Server=(local);Trusted_Connection=yes'',''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job'') q WHERE NAME = ''Backup TLog.Subplan_1'') -- enquando o job estiver executando, ele aguarda 5 segundos e verificad novamente o status WHILE @RunStatus = 1 BEGIN UPDATE TBLOGLIMPDOCS SET StatusAtual = ''Aguardando execucao do job''+@NomJobTLog WAITFOR DELAY ''00:00:05'' SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET (''SQLOLEDB'',''Server=(local);Trusted_Connection=yes'',''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job'') q WHERE NAME = ''Backup TLog.Subplan_1'') END -- executa o job informado EXEC msdb.dbo.sp_start_job @NomJobTLog; -- aguarda o job encerrar para voltar para a procedure de limpeza SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET (''SQLOLEDB'',''Server=(local);Trusted_Connection=yes'',''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job'') q WHERE NAME = ''Backup TLog.Subplan_1'') WHILE @RunStatus = 1 BEGIN UPDATE TBLOGLIMPDOCS SET StatusAtual = ''Aguardando execucao do job''+@NomJobTLog WAITFOR DELAY ''00:00:05'' SET @RunStatus = (SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET (''SQLOLEDB'',''Server=(local);Trusted_Connection=yes'',''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job'') q WHERE NAME = ''Backup TLog.Subplan_1'') END END END'
-- procedimento de limpeza de registros expirados 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 informcoes 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 concilicoes 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 referencia 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 referencia 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 acoes 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); |