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); |
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 no Anexo 1.
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. |