Script de Limpeza SQL 2008

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]

-- Criando estatistica para melhorar o desempenho da limpeza

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

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

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,

[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

) ON [PRIMARY]

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

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

EXEC dbo.sp_executesql @statement = N'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'

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

EXEC dbo.sp_executesql @statement = N'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

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'

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

EXEC dbo.sp_executesql @statement = N'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(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

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

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:

INSERT INTO TBPARLIMDOCS VALUES(365,1000, '00:00:01', 1,0,0,'PROC','C:\BKP_LOG', '', 1);

Voltar