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