SQL Server Maintenance Solution - Automatizando os scripts ola.hallengren

Sergio BenderSergio Bender
7 min read

Table of contents

Os scripts do Ola Hallengren, que são amplamente reconhecidos como uma das melhores práticas para manutenção de bancos de dados no SQL Server.

Para implementar em uma instancia de banco de dados deve baixar o arquivo MaintenanceSolution.sql e rodar no Sql Server.

https://ola.hallengren.com/scripts/MaintenanceSolution.sql

Após rodar o script vai ser criado varios jobs que devem ser ajustador conforme a politica de backup do cliente.,

Para automatizar essa tarefa criei os scripts abaixo para facilitar os ajustes dos stpes e agendamentos.

Se preferir pode usar os scripts abaixo:

1_MaintenanceSolution.sql 2_Altera_jobs_OLA.sql

Os parametros @Directory e @CleanupTime devem ser ajustados conforme a configuração do seu ambiente.

1. Backup Completo dos Bancos de Dados do Sistema - Full

USE msdb;
GO

-- Atualizar o step do job para backup completo dos bancos de dados do sistema
EXEC sp_update_jobstep
    @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseBackup
                @Databases = ''SYSTEM_DATABASES'',
                @Directory = ''H:\Backup'',
                @BackupType = ''FULL'',
                @Verify = ''Y'',
                @Compress = ''Y'',
                @CheckSum = ''Y'',
                @CleanupTime = 120';

-- Adicionar agendamento semanal para o job (domingo às 02:00 AM)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL',
    @name = 'Weekly System Full Backup',
    @freq_type = 8,              -- Executar semanalmente
    @freq_interval = 64,         -- Domingo
    @active_start_time = 020000, -- Hora de início (02:00 AM)
    @freq_recurrence_factor = 1, -- Intervalo: toda semana
    @enabled = 1;

2. Backup Completo dos Bancos de Dados de Usuário - Full

USE msdb;
GO

-- Atualizar o step do job para backup completo dos bancos de dados de usuários
EXEC sp_update_jobstep
    @job_name = 'DatabaseBackup - USER_DATABASES - FULL',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseBackup
                @Databases = ''USER_DATABASES'',
                @Directory = ''H:\Backup'',
                @BackupType = ''FULL'',
                @Verify = ''Y'',
                @Compress = ''Y'',
                @CheckSum = ''Y'',
                @CleanupTime = 120';

-- Adicionar agendamento diário para o job (diariamente às 03:00 AM)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseBackup - USER_DATABASES - FULL',
    @name = 'Daily Full Backup',
    @freq_type = 4,              -- Executar diariamente
    @active_start_time = 030000, -- Hora de início (03:00 AM)
    @freq_interval = 1,          -- Intervalo: a cada 1 dia
    @enabled = 1;

3. Backup Diferencial dos Bancos de Dados de Usuário - Diff

USE msdb;
GO

-- Atualizar o step do job para backup diferencial dos bancos de dados de usuários
EXEC sp_update_jobstep
    @job_name = 'DatabaseBackup - USER_DATABASES - DIFF',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseBackup
                @Databases = ''USER_DATABASES'',
                @Directory = ''H:\Backup'',
                @BackupType = ''DIFF'',
                @Verify = ''Y'',
                @Compress = ''Y'',
                @CheckSum = ''Y'',
                @CleanupTime = 120';

-- Adicionar agendamento diário para o job (diariamente às 12:00 PM)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseBackup - USER_DATABASES - DIFF',
    @name = 'Daily Differential Backup',
    @freq_type = 4,              -- Executar diariamente
    @active_start_time = 120000, -- Hora de início (12:00 PM)
    @freq_interval = 1,          -- Intervalo: a cada 1 dia
    @enabled = 1;

4. Backup de Logs dos Bancos de Dados de Usuário - Log

-- Atualizar o step do job para backup de logs de transação dos bancos de dados de usuários
EXEC sp_update_jobstep
    @job_name = 'DatabaseBackup - USER_DATABASES - LOG',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseBackup
                @Databases = ''USER_DATABASES'',
                @Directory = ''H:\Backup'',
                @BackupType = ''LOG'',
                @Verify = ''Y'',
                @Compress = ''Y'',
                @CheckSum = ''Y'',
                @CleanupTime = 120';

-- Adicionar agendamento para o job (a cada 15 minutos)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseBackup - USER_DATABASES - LOG',
    @name = 'Log Backup Every 15 Minutes',
    @freq_type = 4,              -- Executar diariamente
    @freq_interval = 1,          -- Executar todos os dias
    @freq_subday_type = 4,       -- Executar a cada n minutos
    @freq_subday_interval = 15,  -- Intervalo: a cada 15 minutos
    @active_start_time = 000000, -- Começar à meia-noite
    @active_end_time = 235959,   -- Parar às 23:59:59
    @enabled = 1;

5. Otimização de Índices dos Bancos de Dados de Usuário - IndexOptimize

USE msdb;
GO

-- Atualizar o step do job para otimização de índices dos bancos de dados de usuários
EXEC sp_update_jobstep
    @job_name = 'IndexOptimize - USER_DATABASES',
    @step_id = 1,
    @command = 'EXEC dbo.IndexOptimize
                @Databases = ''USER_DATABASES'',
                @FragmentationLevel1 = 10,
                @FragmentationLevel2 = 30,
                @UpdateStatistics = ''ALL'',
                @OnlyModifiedStatistics = ''Y'',
                @LogToTable = ''Y''';

-- Adicionar agendamento semanal para o job (sábado às 01:00 AM)
EXEC sp_add_jobschedule
    @job_name = 'IndexOptimize - USER_DATABASES',
    @name = 'Weekly Index Optimization',
    @freq_type = 8,              -- Executar semanalmente
    @freq_interval = 32,         -- Sábado
    @active_start_time = 010000, -- Hora de início (01:00 AM)
    @freq_recurrence_factor = 1, -- Intervalo: toda semana
    @enabled = 1;

6.DatabaseIntegrityCheck - SYSTEM_DATABASES

O ideal é executar o check de integridade dos bancos de dados do sistema semanalmente, fora do horário de pico, para garantir que problemas sejam detectados sem impactar o desempenho durante as operações normais.

USE msdb;
GO

-- Atualizar o step do job para verificar a integridade dos bancos de dados do sistema
EXEC sp_update_jobstep
    @job_name = 'DatabaseIntegrityCheck - SYSTEM_DATABASES',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseIntegrityCheck 
                @Databases = ''SYSTEM_DATABASES'', 
                @CheckCommands = ''CHECKDB'',
                @LogToTable = ''Y''';

-- Adicionar agendamento semanal para o job (domingo às 04:00 AM)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseIntegrityCheck - SYSTEM_DATABASES',
    @name = 'Weekly System DB Integrity Check',
    @freq_type = 8,              -- Executar semanalmente
    @freq_interval = 64,         -- Domingo
    @active_start_time = 040000, -- Hora de início (04:00 AM)
    @freq_recurrence_factor = 1, -- Intervalo: toda semana
    @enabled = 1;

7.DatabaseIntegrityCheck - USER_DATABASES

Para os bancos de dados de usuários, a prática recomendada é também executar a verificação semanalmente, preferencialmente fora dos horários comerciais, para evitar sobrecarga no sistema. Escolhemos o sábado à noite como um horário apropriado, já que muitas vezes o sistema está com menos carga operacional.

USE msdb;
GO
-- Atualizar o step do job para verificar a integridade dos bancos de dados de usuários
EXEC sp_update_jobstep
    @job_name = 'DatabaseIntegrityCheck - USER_DATABASES',
    @step_id = 1,
    @command = 'EXEC dbo.DatabaseIntegrityCheck 
                @Databases = ''USER_DATABASES'', 
                @CheckCommands = ''CHECKDB'',
                @LogToTable = ''Y''';
-- Adicionar agendamento semanal para o job (sábado às 02:00 AM)
EXEC sp_add_jobschedule
    @job_name = 'DatabaseIntegrityCheck - USER_DATABASES',
    @name = 'Weekly User DB Integrity Check',
    @freq_type = 8,              -- Executar semanalmente
    @freq_interval = 32,         -- Sábado
    @active_start_time = 020000, -- Hora de início (02:00 AM)
    @freq_recurrence_factor = 1, -- Intervalo: toda semana

8. Criação do Job para Coleta de Estatísticas

O script para configurar o step e o agendamento de um job chamado UpdateStatistics - USER_DATABASES. Ele será executado diariamente em um horário que não sobrecarregue o sistema (geralmente no início da madrugada, logo após os backups diferenciais ou em outro horário de baixa atividade).

USE msdb;
GO

-- Criar o job para a coleta de estatísticas
EXEC sp_add_job
    @job_name = 'UpdateStatistics - USER_DATABASES',
    @enabled = 1,                 -- Habilitar o job
    @description = 'Job para coletar e atualizar as estatísticas dos bancos de dados de usuários';

-- Adicionar um step ao job para a coleta de estatísticas
EXEC sp_add_jobstep
    @job_name = 'UpdateStatistics - USER_DATABASES',
    @step_name = 'Coletar Estatísticas',
    @subsystem = 'TSQL',
    @command = 'EXEC dbo.IndexOptimize 
                @Databases = ''USER_DATABASES'', 
                @UpdateStatistics = ''ALL'',        -- Atualizar todas as estatísticas
                @OnlyModifiedStatistics = ''Y'',    -- Apenas estatísticas modificadas
                @LogToTable = ''Y'';',              -- Logar as operações em tabela
    @on_success_action = 1,                        -- Avançar para o próximo step (se houver)
    @on_fail_action = 2;                           -- Parar o job em caso de falha

-- Agendar o job para ser executado diariamente às 02:30 AM
EXEC sp_add_jobschedule
    @job_name = 'UpdateStatistics - USER_DATABASES',
    @name = 'Daily Statistics Update',
    @freq_type = 4,                -- Executar diariamente
    @active_start_time = 023000,   -- Hora de início (02:30 AM)
    @freq_interval = 1,            -- Intervalo: a cada 1 dia
    @enabled = 1;                  -- Habilitar o agendamento

-- Associar o job ao SQL Server Agent (category_id 0 é a categoria padrão)
EXEC sp_add_jobserver
    @job_name = 'UpdateStatistics - USER_DATABASES',
    @server_name = '(local)';
GO

Resumo das Políticas de Horários:

  • Backup Completo - SYSTEM_DATABASES: semanal, domingo às 02:00 AM.

  • Backup Completo - USER_DATABASES: diário, às 03:00 AM.

  • Backup Diferencial - USER_DATABASES: diário, às 12:00 PM (meio-dia).

  • Backup de Logs - USER_DATABASES: a cada 15 minutos, durante todo o dia.

  • Otimização de Índices - USER_DATABASES: semanal, sábado às 01:00 AM.

  • DatabaseIntegrityCheck - SYSTEM_DATABASES: semanal, domingo às 04:00 AM.

  • DatabaseIntegrityCheck - USER_DATABASES: semanal, sábado às 02:00 AM.

  • Job UpdateStatistics - USER_DATABASES: coleta de estatísticas diárias às 02:30 AM.

Com esses scripts, você estará configurando os steps e os agendamentos para todos os jobs listados. Pode rodar esses comandos diretamente no SQL Server e seus jobs estarão prontos conforme a política que discutimos. Se houver mais ajustes necessários, é só avisar!

0
Subscribe to my newsletter

Read articles from Sergio Bender directly inside your inbox. Subscribe to the newsletter, and don't miss out.

ola.hallengrenSQL Server

Written by

Sergio Bender
Sergio Bender