Script de Rotina de Manutenção de Banco de Dados SQL 2012 e 2014
Anterior
Próximo
Feedback
Imprimir

Script de Rotina de Manutenção de Banco de Dados SQL 2012 e 2014

Arquivo LIMPA_TRANSACTION_LOG

 

CREATE PROCEDURE [LIMPA_TRANSACTION_LOG] @DBName varchar(50)

AS

DECLARE

 @TipLimTLog        VARCHAR(4),

 @DirBkpTLog        VARCHAR(100),

 @NomJobTLog        VARCHAR(50),

 @arquivo VARCHAR(100),

 @RunStatus int

 

BEGIN

 SET NOCOUNT ON;

 SELECT @TipLimTLog = TipLimTLog FROM TBPARMANDB

 SELECT @DirBkpTLog = DirBkpTLog FROM TBPARMANDB

 SELECT @NomJobTLog = NomJobTLog FROM TBPARMANDB

 SET @DBName = (SELECT REPLACE(REPLACE(@DBName,'[',''),']',''))

 

 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

                 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

                 WAITFOR DELAY '00:00:05'

                 EXECUTE sp_MonitorProcessingJob @status = @RunStatus OUTPUT;

         END

 END

END

 

Arquivo TK_MAN_IDX

 

 

USE [master]

GO

if exists (select name from sys.objects where name like 'TK_MAN_IDX') drop procedure TK_MAN_IDX

go

/****** Object:  StoredProcedure [dbo].[TK_MAN_IDX]    Script Date: 08/08/2013 19:25:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[TK_MAN_IDX] @BASE varchar(100),@DIF_MIN INT,@MED_FRAG INT

as

begin

if exists (select name from tempdb.sys.objects where name like '##table_idx')drop table ##table_idx

 

set nocount on

 

DECLARE @TABLE_NAME VARCHAR(100),

@STATS_NAME VARCHAR(100),

@COMANDO NVARCHAR(500),

@HORA_INICIO DATETIME,

@DATABASE_ID int,

@DATABASE_NAME varchar(200),

@IDX_NAME varchar(200),

@Frag int,

@LimpaLog int

 

-- Carrega configurações da TBPARMANDB

SELECT @limpalog = LimTLog FROM TBPARMANDB

 

-- Definindo janela de manutenção

select  @HORA_INICIO= GETDATE()

-- Define comprimento da janela.

--select @DIF_MIN=4

--select @MED_FRAG =10

 

 

-------------------------------- Manutenção pata todas as bases de dados ------------------------

IF @BASE LIKE 'TODAS'

  BEGIn

 

DECLARE man_cursor CURSOR FOR

SELECT database_id,

        '[' + name + ']'

FROM   sys.databases

WHERE  name <> 'MASTER'

        AND name <> 'MODEL'

        AND name <> 'MSDB'

        AND name NOT LIKE'REPORTSERVER%'

        AND name <> 'TEMPDB'

ORDER BY name

 

OPEN man_cursor;

 

FETCH next FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

 

WHILE @@FETCH_STATUS = 0

BEGIN

--------------------------------- Início do tratamento de índices

SELECT @COMANDO = N'SELECT ''[''+obj.name+'']'' tb_name,''[''+b.name+'']'' idx_name,a.avg_fragmentation_in_percent

into ##table_idx FROM '+@DATABASE_NAME+'.sys.dm_db_index_physical_stats ('

+ CONVERT(VARCHAR(4), @DATABASE_ID)

+ ', NULL, NULL, NULL, NULL) AS a

JOIN '+@DATABASE_NAME+'.sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

join '+@DATABASE_NAME+'.sys.objects as obj on a.object_id=obj.object_id

order by avg_fragmentation_in_percent desc;'

exec sp_executesql @comando

--select * from ##table_idx

 

DECLARE MAN_IDX CURSOR FOR

select tb_name,idx_name,avg_fragmentation_in_percent from ##table_idx where avg_fragmentation_in_percent >= @MED_FRAG

 

OPEN MAN_IDX

 

FETCH NEXT FROM MAN_IDX INTO @TABLE_NAME,@IDX_NAME,@Frag

 

WHILE @@FETCH_STATUS = 0

BEGIN

 -- Controle de execução da rotina em horário próprio

 IF DATEDIFF(MINUTE,@HORA_INICIO,GETDATE()) <= @DIF_MIN

 BEGIN

         begin try

                 SET @COMANDO =NULL

                 select @COMANDO = N'alter index '+@IDX_NAME+' on '+@DATABASE_NAME+'..'+@table_name+' REORGANIZE'

                 EXEC SP_EXECUTESQL @COMANDO

                 INSERT INTO MASTER..TK_IDX_HIST VALUES (@DATABASE_NAME,@table_name,@IDX_NAME,@Frag,GETDATE())

         end try

         begin catch

                 SELECT

                 ERROR_NUMBER() AS ErrorNumber,

                 ERROR_MESSAGE() AS ErrorMessage;

         end catch

         -- Limpeza do Log

         IF @Limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @DATABASE_NAME

 END

FETCH NEXT FROM MAN_IDX INTO @TABLE_NAME,@IDX_NAME,@Frag

END

 

CLOSE MAN_IDX

DEALLOCATE MAN_IDX

DROP TABLE ##table_idx

 

FETCH NEXT FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

END

 

CLOSE man_cursor

DEALLOCATE man_cursor

 

set nocount off

end

-- Fim do IF para @BASE='TODAS'

-------------------------------- Manutenção em base de dados específica ------------------------

IF @base <> 'TODAS'

  BEGIN

      SET @DATABASE_NAME='['+@base+']'

        IF EXISTS (SELECT Lower(name)

                  FROM   sys.databases

                  WHERE  name LIKE @base)

        BEGIN

        --select @DATABASE_NAME AS 'DATABASE'

             

DECLARE man_cursor CURSOR FOR

SELECT database_id,

        '[' + name + ']'

FROM   sys.databases

WHERE  name like @BASE

 

OPEN man_cursor;

 

FETCH next FROM man_cursor INTO @DATABASE_ID, @base

 

WHILE @@FETCH_STATUS = 0

BEGIN

--------------------------------- Início do tratamento de índices

SELECT @COMANDO = N'SELECT ''[''+obj.name+'']'' tb_name,''[''+b.name+'']'' idx_name,a.avg_fragmentation_in_percent

into ##table_idx FROM '+@DATABASE_NAME+'.sys.dm_db_index_physical_stats ('

+ CONVERT(VARCHAR(4), @DATABASE_ID)

+ ', NULL, NULL, NULL, NULL) AS a

JOIN '+@DATABASE_NAME+'.sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

join '+@DATABASE_NAME+'.sys.objects as obj on a.object_id=obj.object_id

order by avg_fragmentation_in_percent desc;'

exec sp_executesql @comando

--select * from ##table_idx

 

DECLARE MAN_IDX CURSOR FOR

select tb_name,idx_name,avg_fragmentation_in_percent from ##table_idx where avg_fragmentation_in_percent >= @MED_FRAG

 

OPEN MAN_IDX

 

FETCH NEXT FROM MAN_IDX INTO @TABLE_NAME,@IDX_NAME,@Frag

 

WHILE @@FETCH_STATUS = 0

BEGIN

 -- Controle de execução da rotina em horário próprio

 IF DATEDIFF(MINUTE,@HORA_INICIO,GETDATE()) <= @DIF_MIN

 BEGIN

         begin try

                 SET @COMANDO =NULL

                 select @COMANDO = N'alter index '+@IDX_NAME+' on '+@DATABASE_NAME+'..'+@table_name+' REORGANIZE'

                 EXEC SP_EXECUTESQL @COMANDO

                 INSERT INTO MASTER..TK_IDX_HIST VALUES (@DATABASE_NAME,@table_name,@IDX_NAME,@Frag,GETDATE())

         end try

         begin catch

                 SELECT

                 ERROR_NUMBER() AS ErrorNumber,

                 ERROR_MESSAGE() AS ErrorMessage;

         end catch

         -- Limpeza do Log

         IF @Limpalog = 1 EXEC LIMPA_TRANSACTION_LOG @DATABASE_NAME

 END

FETCH NEXT FROM MAN_IDX INTO @TABLE_NAME,@IDX_NAME,@Frag

END

 

CLOSE MAN_IDX

DEALLOCATE MAN_IDX

DROP TABLE ##table_idx

 

FETCH NEXT FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

END

 

CLOSE man_cursor

DEALLOCATE man_cursor

 

set nocount off

 

end

 

ELSE

        BEGIN

            PRINT 'Base de dados ou opção não existe !

Use @base=''todas'' para manutenção em todas as bases de dados ou @base=''nomebase'' para uma base específica.'

        END

-- Fim da

end

-- End fim da procedure

DELETE FROM MASTER..TK_IDX_HIST WHERE DATA < GETDATE()-60

end

 

Arquivo TK_MAN_STS

 

 

use master

go

if exists (select name from sys.objects where name like 'TK_MAN_STATS') drop procedure TK_MAN_STATS

go

create procedure TK_MAN_STATS @BASE varchar(100),@DIF_MIN INT,@DUPD_STATS INT,@VNULL varchar(2)

as

begin

if exists (select name from tempdb.sys.objects where name like '##stats_table')drop table ##stats_table

 

set nocount on

DECLARE @TABLE_NAME VARCHAR(100),

@STATS_NAME VARCHAR(100),

@COMANDO NVARCHAR(500),

@HORA_INICIO DATETIME,

@DATABASE_ID INT,

@LAST_UPD DATETIME,

@DATABASE_NAME VARCHAR(200)

--@MED_FRAG INT,

--@DUPD_STATS INT,

--@DIF_MIN INT

 

-- Definindo janela de manutenção

select @HORA_INICIO= GETDATE()

-- Define comprimento da janela.

--select @DIF_MIN=4

--select @DUPD_STATS =50

 

-------------------------------- Manutenção pata todas as bases de dados ------------------------

IF @BASE LIKE 'TODAS'

  BEGIN

 

DECLARE man_cursor CURSOR FOR

SELECT database_id,

        '[' + name + ']'

FROM   sys.databases

WHERE name <> 'MASTER'

        AND name <> 'MODEL'

        AND name <> 'MSDB'

        AND name NOT LIKE'REPORTSERVER%'

        AND name <> 'TEMPDB'

ORDER BY name

 

OPEN man_cursor;

 

FETCH next FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

 

WHILE @@FETCH_STATUS = 0

BEGIN

-------------------------------- Início do tratamento de estatísticas --------------------

 

set @COMANDO=null

if @VNULL = 's'

begin

select @comando = 'select  ''[''+obj.name+'']'' TableName,''[''+stat.name+'']'' as StatName,STATS_DATE(obj.OBJECT_ID, stat.stats_id) AS LastUpdate

into ##stats_table

from '+@database_name+'.sys.stats stat inner join '+@DATABASE_NAME+'.sys.objects obj on stat.object_id=obj.object_id

where obj.type=''u''

order by LastUpdate asc'

EXEC SP_EXECUTESQL @COMANDO

end

else

begin

select @comando = 'select  ''[''+obj.name+'']'' TableName,''[''+stat.name+'']'' as StatName,STATS_DATE(obj.OBJECT_ID, stat.stats_id) AS LastUpdate

into ##stats_table

from '+@database_name+'.sys.stats stat inner join '+@DATABASE_NAME+'.sys.objects obj on stat.object_id=obj.object_id

where obj.type=''u''

and STATS_DATE(obj.OBJECT_ID, stat.stats_id) is not NULL

order by LastUpdate asc'

EXEC SP_EXECUTESQL @COMANDO

end

--select * from ##STATS_TABLE

 

 

DECLARE MAN_STATS CURSOR FOR

SELECT TABLENAME,STATNAME,LastUpdate FROM ##STATS_TABLE WHERE DATEDIFF(DAY,LASTUPDATE,GETDATE()) >= @DUPD_STATS

 

OPEN MAN_STATS

 

FETCH NEXT FROM MAN_STATS INTO @TABLE_NAME,@STATS_NAME,@Last_Upd

 

WHILE @@FETCH_STATUS = 0

BEGIN

 -- Controle de execução da rotina em horário próprio

 IF DATEDIFF(MINUTE,@HORA_INICIO,GETDATE()) <= @DIF_MIN

 BEGIN

         begin try

                 SET @COMANDO =NULL

                 select @COMANDO = 'UPDATE STATISTICS '+@DATABASE_NAME+'..'+@TABLE_NAME+'('+@STATS_NAME+')'

                 EXEC SP_EXECUTESQL @COMANDO

                 INSERT INTO MASTER..TK_STAT_HIST VALUES (@DATABASE_NAME,@TABLE_NAME,@STATS_NAME,@Last_Upd,GETDATE())

         end try

      begin catch

                 SELECT

                 ERROR_NUMBER() AS ErrorNumber,

                 ERROR_MESSAGE() AS ErrorMessage;

      end catch

 END

FETCH NEXT FROM MAN_STATS INTO @TABLE_NAME,@STATS_NAME,@Last_Upd

END

 

CLOSE MAN_STATS

DEALLOCATE MAN_STATS

DROP TABLE ##stats_table

 

FETCH NEXT FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

END

 

CLOSE man_cursor

DEALLOCATE man_cursor

 

set nocount off

-- End fim da procedure

end

 

-- Fim do IF para @BASE='TODAS'

 

 

-- Manutenção em base de dados específica.

IF @base <> 'TODAS'

  BEGIN

      SET @DATABASE_NAME='['+@base+']'

        IF EXISTS (SELECT Lower(+name)

                  FROM   sys.databases

                  WHERE name LIKE @base)

        BEGIN

        --select @DATABASE_NAME AS 'DATABASE'

     

DECLARE man_cursor CURSOR FOR

SELECT database_id,

        '[' + name + ']'

FROM   sys.databases

WHERE name like @BASE

 

OPEN man_cursor;

 

FETCH next FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

 

WHILE @@FETCH_STATUS = 0

BEGIN

-------------------------------- Início do tratamento de estatísticas --------------------

 

set @COMANDO=null

if @VNULL = 's'

begin

select @comando = 'select  ''[''+obj.name+'']'' TableName,''[''+stat.name+'']'' as StatName,STATS_DATE(obj.OBJECT_ID, stat.stats_id) AS LastUpdate

into ##stats_table

from '+@database_name+'.sys.stats stat inner join '+@DATABASE_NAME+'.sys.objects obj on stat.object_id=obj.object_id

where obj.type=''u''

order by LastUpdate asc'

EXEC SP_EXECUTESQL @COMANDO

end

else

begin

select @comando = 'select  ''[''+obj.name+'']'' TableName,''[''+stat.name+'']'' as StatName,STATS_DATE(obj.OBJECT_ID, stat.stats_id) AS LastUpdate

into ##stats_table

from '+@database_name+'.sys.stats stat inner join '+@DATABASE_NAME+'.sys.objects obj on stat.object_id=obj.object_id

where obj.type=''u''

and STATS_DATE(obj.OBJECT_ID, stat.stats_id) is not NULL

order by LastUpdate asc'

EXEC SP_EXECUTESQL @COMANDO

end

--select * from ##STATS_TABLE

 

 

DECLARE MAN_STATS CURSOR FOR

SELECT TABLENAME,STATNAME,LastUpdate FROM ##STATS_TABLE WHERE DATEDIFF(DAY,LASTUPDATE,GETDATE()) >= @DUPD_STATS OR (LASTUPDATE is NULL AND @VNULL = 's')

 

OPEN MAN_STATS

 

FETCH NEXT FROM MAN_STATS INTO @TABLE_NAME,@STATS_NAME,@Last_Upd

 

WHILE @@FETCH_STATUS = 0

BEGIN

 -- Controle de execução da rotina em horário próprio

 IF DATEDIFF(MINUTE,@HORA_INICIO,GETDATE()) <= @DIF_MIN

 BEGIN

         begin try

                 SET @COMANDO =NULL

                 select @COMANDO = 'UPDATE STATISTICS '+@DATABASE_NAME+'..'+@TABLE_NAME+'('+@STATS_NAME+')'

                 EXEC SP_EXECUTESQL @COMANDO

                 INSERT INTO MASTER..TK_STAT_HIST VALUES (@DATABASE_NAME,@TABLE_NAME,@STATS_NAME,@Last_Upd,GETDATE())

         end try

      begin catch

                 SELECT

                 ERROR_NUMBER() AS ErrorNumber,

                 ERROR_MESSAGE() AS ErrorMessage;

      end catch

 END

FETCH NEXT FROM MAN_STATS INTO @TABLE_NAME,@STATS_NAME,@Last_Upd

END

 

CLOSE MAN_STATS

DEALLOCATE MAN_STATS

DROP TABLE ##stats_table

 

FETCH NEXT FROM man_cursor INTO @DATABASE_ID, @DATABASE_NAME

END

 

CLOSE man_cursor

DEALLOCATE man_cursor

 

set nocount off

 

end

 

ELSE

        BEGIN

            PRINT 'Base de dados ou opção não existe !

Use @base=''todas'' para manutenção em todas as bases de dados ou @base=''nomebase'' para uma base específica.'

        END

-- Fim da

end

-- End fim da procedure

DELETE FROM MASTER..TK_STAT_HIST WHERE DATA < GETDATE()-60

end