Script para SQL Server 2012 e 2014

Anterior
Próximo
Feedback
Imprimir

Script para SQL Server 2012 e 2014

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

 

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

USE [<BANCO_DE_DADOS>]

GO

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

GO

 

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

AS

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]

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

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

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

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

 @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