-- Habilitando o Ad Hoc Distributed Queries sp_configure 'show advanced options', 1 RECONFIGURE; GO
sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE; GO
CREATE TABLE [dbo].[TmpOids]( [OID] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ( [OID] 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].[TBLOGLIMPDOCSCOLD]( [IdLimpDocs] [int] IDENTITY(1,1) NOT NULL, [HorIniLim] [datetime] NOT NULL, [DiaVidDoc] [int] NULL, [BulkLogged] [int] NULL, [LimTLog] [int] NULL, [TipLimTLog] [varchar](4) NULL, [DirBkpTLog] [varchar](100) NULL, [NomJobTLog] [varchar](50) NULL, [TotExcTbCold] [int] NULL, [TotExcTbColdEvt] [int] NULL, [TotLoopDocumentos] [int] NULL, [HorFimLim] [datetime] NULL, [StatusAtual] [varchar](100) NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[TBLOGMSGSCOLD]( [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].[TBPARLIMCOLD]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [DiaVidDoc] [int] NOT NULL, [QtdRegLimLoo] [int] NOT NULL, [TemAtrLoo] [char](8) 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, [ColdTbName] [varchar](50) NOT NULL, [ColdEvtTbName] [varchar](50) NOT NULL, [Doc] [varchar](4) NOT NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[TBPARLIMCOLD] ADD CONSTRAINT [DF_TBPARLIMCOLD_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 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[ESCREVE_LOG] @IdLimDocs int, @TxtLog varchar(500) AS BEGIN SET NOCOUNT ON; INSERT INTO TBLOGMSGSCOLD (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), @TxtLog) END GO
-- Criar procedimento de limpeza do transaction log, caso [TBPARLIMCOLD].[LimTLog] = 1 /****** Object: StoredProcedure [dbo].[LIMPA_TRANSACTION_LOG_COLD] Script Date: 09/23/2012 22:26:00 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LIMPA_TRANSACTION_LOG_COLD]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG_COLD] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- procedimento de limpeza do transaction log, caso [TBPARLIMCOLD].[LimTLog] = 1 CREATE PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG_COLD] @IdLimDocs int, @IdParlimCold 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 TBPARLIMCOLD where id = @IdParlimCold SELECT @DirBkpTLog = DirBkpTLog FROM TBPARLIMCOLD where id = @IdParlimCold SELECT @NomJobTLog = NomJobTLog FROM TBPARLIMCOLD where id = @IdParlimCold SELECT @DBName = DB_NAME()
UPDATE TBLOGLIMPDOCSCOLD SET TipLimTLog = @TipLimTLog, DirBkpTLog = @DirBkpTLog, NomJobTLog = @NomJobTLog WHERE IdLimpDocs = @IdLimDocs
UPDATE TBLOGLIMPDOCSCOLD SET StatusAtual = 'Limpando Transaction Log via '+@TipLimTLog
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 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 TBLOGLIMPDOCSCOLD SET StatusAtual = 'Aguardando execução 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 TBLOGLIMPDOCSCOLD SET StatusAtual = 'Aguardando execução 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 GO
/****** Object: StoredProcedure [dbo].[LIMPA_REGISTROS_COLD] Script Date: 09/23/2012 22:42:09 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LIMPA_REGISTROS_COLD]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[LIMPA_REGISTROS_COLD] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[LIMPA_REGISTROS_COLD] @CONFIG INT AS
DECLARE @DBName varchar(50), @IdLimDocs int, @diasdevida int, @totalregistro int, @bulkLogged int, @limpalog int, @qtdloop int, @sql varchar(1000), @sqlCountOids nvarchar(1000), @sqlDelete nvarchar(500), @RowCount INT, @CountLoop INT, @totalLimpezaLoop BIGINT, @Debug BIT, @tempodeatraso char(8), @tiplimlog varchar(4), @MsgLog INT, @InicioExec datetime, @ColdTbName VARCHAR(50), @ColdEvtTbName VARCHAR(50), @CountDelete INT, @Doc varchar(4), @DhemiColumn varchar(50), @LogStr varchar(200)
SET NOCOUNT ON SET XACT_ABORT ON Select @DBName = DB_NAME()
-- carrega as variáreis de acordo com a tabela TBPARLIMCOLD SELECT @diasdevida = DiaVidDoc, @totalregistro = QtdRegLimLoo, @tempodeatraso = TemAtrLoo, @limpalog = LimTLog, @bulkLogged = BulkLogged, @ColdTbName = ColdTbName, @ColdEvtTbName = ColdEvtTbName, @InicioExec = GETDATE(), @Doc = Doc, @MsgLog = MsgLog FROM TBPARLIMCOLD WHERE Id = @CONFIG
-- insere a linha de log para a execução INSERT INTO TBLOGLIMPDOCSCOLD (HorIniLim,DiaVidDoc,BulkLogged,LimTLog,TotExcTbCold,TotExcTbColdEvt,TotLoopDocumentos) values (GETDATE(),@diasdevida,@bulkLogged,@limpalog,0,0,0) -- captura o id da execução na tabela TBPARLIMCOLD SELECT @IdLimDocs = (SELECT TOP 1 IdLimpDocs FROM TBLOGLIMPDOCSCOLD ORDER BY 1 DESC)
--Elimina logs da última execução TRUNCATE TABLE TBLOGMSGSCOLD
--Altera Forma de log de banco IF @bulkLogged = 1 ALTER DATABASE [<DATABASE_NAME>] SET RECOVERY BULK_LOGGED WITH NO_WAIT IF @bulkLogged = 1 IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Alterou o Recovery Model para BULK_LOGGED'
IF (UPPER(@Doc) = 'NFE') BEGIN SET @DhemiColumn = 'IDE_DEMI' END ELSE BEGIN SET @DhemiColumn = 'IDE_DHEMI' END
IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Início da Limpeza'
SET @qtdloop = 0 -- início da captura de limites de informções
IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Verificado total de registros para limpezas'
-- INICIO DA EXCLUSAO DE DOCUMENTOS EXPIRADOS SET @qtdloop = 0 UPDATE TBLOGLIMPDOCSCOLD SET StatusAtual = 'Inicio da limpeza de documentos expirados' where IdLimpDocs = @IdLimDocs
SET @totalLimpezaLoop = @totalregistro SELECT @sqlCountOids = 'select @cnt=count(oid) FROM ' + CONVERT(varchar(50),@ColdTbName) + '(NOLOCK) WHERE ' + CONVERT(varchar(50),@DhemiColumn) + ' < convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' order by 1' EXECUTE sp_executesql @sqlCountOids, N'@cnt int OUTPUT', @cnt=@CountLoop OUTPUT
SET @LogStr = 'Total de documentos expirados encontrados: ' + CAST((@CountLoop) as varchar(18)) IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, @LogStr
WHILE @CountLoop > 0 BEGIN -- limpa tabelas para seleção dos registros IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Iniciado laço de limpeza do Cold' TRUNCATE TABLE TmpOids
INSERT INTO TBLOGMSGSCOLD (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), 'Capturados valores mínimos')
SET @qtdloop = @qtdloop + 1 BEGIN IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Iniciou backup' EXEC LIMPA_TRANSACTION_LOG_COLD @IdLimDocs, @CONFIG IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Limpou logs de transação e vai inserir registros de tmpLogsDocIds' END
UPDATE TBLOGLIMPDOCSCOLD SET StatusAtual = 'Carga dos documentos expirados' where IdLimpDocs = @IdLimDocs
SELECT @sql = 'INSERT TmpOids SELECT TOP ' + CONVERT(varchar(10),@totalregistro) + ' Oid FROM ' + CONVERT(varchar(50),@ColdTbName) + ' (NOLOCK) WHERE ' + CONVERT(varchar(50),@DhemiColumn) + ' < convert(datetime, ''' + CONVERT(varchar(23),@InicioExec, 120) + ''', 120) - ' + CONVERT(varchar(10),@diasdevida) + ' order by 1' EXECUTE (@sql)
SELECT @totalLimpezaLoop = count(*) from TmpOids
SET @LogStr = 'Inseriu registros na tmpLogsDocIds -> ' + CAST((@totalLimpezaLoop) as varchar(18)) IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, @LogStr
IF @totalLimpezaLoop = 0 BEGIN IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Encerrada a limpeza de documentos normais. Não existem documentos expirados no período de tempo especificado' BREAK END
/* INÍCIO DAS EXCLUSÕES */
-- remove os registros de eventos que estão vinculados aos tmpLogsDocIds que serão removidos --DELETE FROM @coldEvtTbName WHERE OID IN (SELECT OID FROM TmpOids) SELECT @sqlDelete = 'DELETE FROM ' + CONVERT(varchar(50),@ColdEvtTbName) + ' WHERE OID IN (SELECT OID FROM TMPOIDS)' EXECUTE sp_executesql @sqlDelete, N'@cnt int OUTPUT', @cnt=@CountDelete OUTPUT UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbColdEvt = TotExcTbColdEvt + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da tablea de eventos'
-- remove os processos em lotes de eventos que estão vinculados aos ProcessIDs que serão removidos SELECT @sqlDelete = 'DELETE FROM ' + CONVERT(varchar(50),@ColdTbName) + ' WHERE OID IN (SELECT OID FROM TMPOIDS)' EXECUTE sp_executesql @sqlDelete, N'@cnt int OUTPUT', @cnt=@CountDelete OUTPUT UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbCold = TotExcTbCold + @@ROWCOUNT WHERE IdLimpDocs = @IdLimDocs IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Deletou da tabela cold'
/* FIM DAS EXCLUSÕES */
WAITFOR DELAY @tempodeatraso
EXECUTE sp_executesql @sqlCountOids, N'@cnt int OUTPUT', @cnt=@CountLoop OUTPUT END
TRUNCATE TABLE tmpOids UPDATE TBLOGLIMPDOCSCOLD SET TotLoopDocumentos = @qtdloop where IdLimpDocs = @IdLimDocs -- FINAL DA EXCLUSAO DE DOCUMENTOS EXPIRADOS
UPDATE TBLOGLIMPDOCSCOLD SET HorFimLim = GETDATE(), StatusAtual = 'Limpeza Finalizada' where IdLimpDocs = @IdLimDocs IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Finalizou Limpeza'
-- VOLTANDO BACKUP LOG IF @bulkLogged = 1 ALTER DATABASE [<DATABASE_NAME>] SET RECOVERY FULL WITH NO_WAIT IF @bulkLogged = 1 IF @MsgLog = 1 EXEC ESCREVE_LOG @IdLimDocs, 'Alterou o Recovery Model para FULL' |