Script para SQL Server 2012 e 2014

-- Habilitando o Ad Hoc Distributed Queries

sp_configure 'show advanced options', 1

RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE;

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]

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

CREATE TABLE [dbo].[TBPARLIMCOLD](

[Id] [bigint] IDENTITY(1,1) NOT NULL,

[DiaVidDoc] [int] NOT NULL,

[QtdRegLimLoo] [int] NOT NULL,

[TemAtrLoo] [time](7) 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]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[TBPARLIMCOLD] ADD CONSTRAINT [DF_TBPARLIMCOLD_MsgLog] DEFAULT ((0)) FOR [MsgLog]

-- Criar procedimento para escrita de logs

USE [<BANCO_DE_DADOS>]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[ESCREVE_LOG] @IdLimDocs int, @TxtLog varchar(500)

BEGIN

SET NOCOUNT ON;

INSERT INTO TBLOGMSGSCOLD (IdLOGLIMPDOCS, HorLog, TxtMsg) values (@IdLimDocs, GETDATE(), @TxtLog)

END

-- Criar procedimento de limpeza monitoramento de job

/****** Object: StoredProcedure [dbo].[sp_MonitorProcessingJob] Script Date: 09/23/2012 22:26:00 ******/

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]

CREATE procedure [dbo].[sp_MonitorProcessingJob](@status int OUTPUT)

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

-- procedimento de limpeza do transaction log, caso [TBPARLIMCOLD].[LimTLog] = 1

CREATE PROCEDURE [dbo].[LIMPA_TRANSACTION_LOG_COLD] @IdLimDocs int, @IdParLimCold 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 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 WHERE IdLimpDocs = @IdLimDocs

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

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 TBLOGLIMPDOCSCOLD SET StatusAtual = 'Aguardando execução 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 TBLOGLIMPDOCSCOLD SET StatusAtual = 'Aguardando execução do job'+@NomJobTLog WHERE IdLimpDocs = @IdLimDocs

WAITFOR DELAY '00:00:05'

EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

END

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/****** Object: StoredProcedure [dbo].[LIMPA_REGISTROS_COLD] Script Date: 22/01/2015 18:20:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[LIMPA_REGISTROS_COLD] @CONFIG INT

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),

@LogStr varchar(200),

@InicioExec datetime,

@ColdTbName VARCHAR(50),

@ColdEvtTbName VARCHAR(50),

@CountDelete INT,

@Doc varchar(4),

@DhemiColumn varchar(50),

@MsgLog INT

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

IF @limpalog = 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'

Download do Script SQL Server 2012 e 2014