Script para SQL Server 2005 e 2008

Anterior
Próximo
Feedback
Imprimir

Script para SQL Server 2005 e 2008

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

 

Download do Script SQL Server 2005 e 2008