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