|
Durante a manutenção, depois da reorganização de cada índice é realizado o backup do transaction log, dado ao fato de que essa operação infla o espaço físico do banco e compromete o desempenho. Os parâmetros de configuração do backup precisam ser configurados em uma tabela conforme explicação abaixo:
Tabela dos dados de configuração da limpeza do transaction log
CREATE TABLE [dbo].[TBPARMANDB](
[LimTLog] [int] NOT NULL,
[TipLimTLog] [varchar](4) NULL,
[DirBkpTLog] [varchar](100) NULL,
[NomJobTLog] [varchar](50) NULL,
) ON [PRIMARY]
GO
|
LimTLog – Especifica se o Transaction Log deve ser limpo ou não. Valor 1 para SIM e 0 para NÃO.
TipLimTLog – Este campo é referente ao tipo de limpeza do transaction log. Sendo que ele pode ser realizado pelo próprio job, neste caso o campo deve ser informado o valor PROC. Caso esse campo possua valor nulo será chamado outro job (informado no campo NomJobTLog) para realizar o trabalho de backup.
DirBkpTLog – Local onde o backup do Transaction Log será salvo.
NomJobTLog – Neste campo deve ser informado o nome do Job que executará o script de limpeza do Transaction Log.
Após criar a tabela devem ser inseridos os dados conforme modelo abaixo. Neste caso o backup é realizado pelo mesmo job que está executando a manutenção do banco.
INSERT INTO TBPARMANDB VALUES(1, 'PROC', 'D:\backup', NULL);
|
 Importante:
| • | Dependendo da configuração do ambiente do servidor SQL, o script de criação da procedure de limpeza do transaction log pode não compilar gerando erro na conexão do provedor OLEDB com o servidor vinculado, neste caso deve-se alterar o valor do campo server para o nome da instância SQL. |
|
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'
ou
SELECT CURRENT_EXECUTION_STATUS FROM OPENROWSET ('SQLOLEDB','Server=SERVSQLPD01\SQLNDD_PD;Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q WHERE NAME = 'Backup TLog.Subplan_1'
|
Tabela para registro de log dos índices que tiveram manutenção.
CREATE TABLE [dbo].[TK_IDX_HIST](
[Db_name] [varchar](200) NULL,
[Tb_name] [varchar](200) NULL,
[Idx_name] [varchar](200) NULL,
[Frag] [int] NULL,
[Data] [datetime] NULL
) ON [PRIMARY]
GO
|
Tabela para registro de log das estatísticas que tiveram manutenção
CREATE TABLE [dbo].[TK_STAT_HIST](
[Db_name] [varchar](200) NULL,
[Tb_name] [varchar](200) NULL,
[Stat_name] [varchar](200) NULL,
[Last_Upd] [datetime] NULL,
[Data] [datetime] NULL
) ON [PRIMARY]
GO
|
Após isso deve-se rodar o script de criação das procedures contidas abaixo. Existe uma diferenciação de versionamento de bancos, portanto verifique e ceritifque-se de que o script que estará utilizando é realmente o correto para sua base de dados.
Importante:
| • | O horário da rotina de manutenção não deve coincidir com o horário de atividades de backup (dados ou de log). No escopo da rotina de manutenção já existe o backup de log. Se houver essa coincidência de horários a manutenção será abortada. |
|
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
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
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
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
|
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
|
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
end
|
|
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
|
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
|
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
end
|
|
Voltar
|
