Ola Hallengren – Instalar y configurar los jobs de mantenimiento

Los jobs de mantenimiento nos ahorran trabajo y nos hacen la vida más fácil a los DBA ya que nos automatizar tareas que serian un horror de tener que hacerlas manualmente. En el pasado lo más normal era crear planes de mantenimiento en el SQL Server Management Studio, pero los jobs de Ola Hallegren llegaron para hacernos la vida mucho más sencilla.

¿Qué son los jobs de mantenimiento Ola Hallengren?

Los jobs de mantenimiento Ola Hallengres son una serie de jobs que se pueden configurar y programar usando el SQL Agent y que podemos usar para realizar las tareas de mantenimiento de nuestras bases de datos en un instancia SQL Server (integridad, índices , estadísticas) además de configurar, si queremos, los backups para de nuestras bases de datos.

Estos trabajos se basan en stored procedures (procedimientos almacenados) y son usados por muchísimas empresas.

Lo mejor de todo, es totalmente gratis.

Cómo instalar y configurar los jobs de mantenimiento Ola Hallengren

La instalación de los job es bastante sencilla mientras que la configuración variará de acuerdo a las necesidades de vuestros clientes. En este articulo os explicaré la configuración que yo considero normal.

Instalar los jobs de mantenimiento Ola Hallengren

1- Descargaremos el script de instalación «MaintenanceSolution.sql» de la web oficial de Ola Hallengren. Este script, previa configuración, creará los objetos y los jobs que necesitaremos.

2- Los objetos que este script va a crear necesitarán ser creados en una base de datos, mi recomendación es crear una base de datos especifica para esta tarea, en mi caso usaré el nombre «DBMaint».

3- Configuración del «MaintenanceSolution.sql» script.

Una vez que abramos el archivo nos encontremos con el código de arriba. En este código podremos modificar 6 parámetros de acuerdo a nuestras necesidades.

• Especificaremos la base de datos donde se van a crear los objetos, en mi caso cambiaré «USE [Master]» por «USE [DBMaint]».
• Mantendremos la «Y» en @CreateJobs para que los jobs sean creados.
• @BackupDirectory: Aquí especificaremos la ruta donde queremos que sean guardados nuestros backups en caso de que queramos usar los jobs de backups
• @CleanupTime: El tiempo de retención de los backups.
• @OutputFileDirectory: Especificaremos la ruta donde se guardarán los logs de las ejecuciones de estos jobs. Muy importante para investigar posible fallos.
• @LogToTable: Los logs también serán almacenados en una tabla para que podamos consultarlos.

El script crea una tabla (dbo.CommandLog), una función y cuatro procedimientos almacenados en la base de datos DBMaint. También crea 11 jobs del SQL Server Agent, estos jobs son creados sin un horario de ejecución y deben configurarse manualmente.

Configuración de los jobs Ola Hallengren

El script crea por defecto los siguientes jobs:

• CommandLog Cleanup
• Output File Cleanup
• Sp_delete_backuphistory
• sp_purge_jobhistory
• DatabaseBackup – SYSTEM_DATABASES – FULL
• DatabaseIntegrityCheck – SYSTEM_DATABASES
• IndexOptimize – USER_DATABASES
• DatabaseIntegrityCheck – USER_DATABASES
• DatabaseBackup – USER_DATABASES – FULL
• DatabaseBackup – USER_DATABASES – DIFF
• DatabaseBackup – USER_DATABASES – LOG

Y personalmente también creo el siguiente job de forma adicional:

• UpdateStatistics – USER_DATABASES

A continuación pasaré a detallar la configuración de cada job.

IndexOptimize – USER_DATABASES

IndexOptimize es el procedimiento almacenado de SQL Server Maintenance Solution para reconstruir y reorganización de índices y actualización de estadísticas.

Utiliza el comando ALTER INDEX de SQL Server:

• REBUILD WITH (ONLINE = ON) para reconstruir índices en línea.
• REBUILD WITH (ONLINE = OFF) para reconstruir índices fuera de línea
• REORGANIZE para reorganizar los índices.

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@MinNumberOfPages = 1000,
@LogToTable = 'Y'

Los parámetros configurados son los siguientes:

@Databases: Selecciona las bases de datos. Las opciones son SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES. Se admiten AVAILABILITY_GROUP_DATABASES. El carácter (-) se utiliza para excluir bases de datos, y el carácter (%) se utiliza para la selección de comodines. Todas estas operaciones pueden combinarse usando la coma (,).

@FragmentationLow/Medium/High: Especifica las operaciones de mantenimiento que se realizarán según el índice de fragmentación del index.

@FragmentationLevel1: El porcentaje más bajo que debe tener para fragmentación media.
@FragmentationLevel2: El porcentaje más bajo que debe tener para fragmentación alta.

@MinNumberOfPages: Indica el numero de páginas mínimas que debe tener le índice para que se le aplique mantenimiento.

@LogToTable: Añade los logs de los comandos a la table dbo.CommandLog.

UpdateStatistics – USER_DATABASES

Este job es una modificación del anterior que crearé manualmente y se encarga de actualizar las estadísticas.

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100,
@Indexes = 'ALL_INDEXES ',
@LogToTable = 'Y'

El propietario de este job será el usuario «sa» y la categoría de este job será «Database Maintenance». Habrá que indicarle que la base de datos donde almacene los objetos sea la misma que usamos en la configuración inicial, en mi caso «DBMaint».

Como última configuración deberemos añadir el path del archivo de log en la pestaña de «avanzado«, en mi caso es el siguiente:

$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

Esto creará un archivo de texto con la información de cada ejecución.

Los parámetros destacables de este job son los siguientes:

@UpdateStatistics: Indicas que las estadísticas deben ser actualizadas.

Los valores disponibles son:
ALL: Actualizar estadísticas de índices y columnas.
INDEX: Sólo estadísticas de índices.
COLUMNS: Solo estadísticas de columnas.

@OnlyModifiedStatistics: Actualizar sólo las estadísticas que hayan sido modificadas desde la última vez que se actualizaron.
@Indexes: Los índices seleccionados, si no se especifica ninguno se seleccionarán todos.

DatabaseIntegrityCheck – SYSTEM_DATABASES

Este es el job que se encarga de comprobar la integridad de las bases de datos, en este caso de las bases de datos de sistema.

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'SYSTEM_DATABASES',
@CheckCommands = ‘CHECKDB’,
@LogToTable = 'Y'

DatabaseIntegrityCheck – SYSTEM_DATABASES

Este es el job que se encarga de comprobar la integridad de las bases de datos, en este caso de las bases de datos del usuario.

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@CheckCommands = ‘CHECKDB’,
@LogToTable = 'Y'

CommandLog Cleanup

Elimina los items de la tabla CommandLog más antiguos de 30 días.

DELETE FROM [dbo].[CommandLog]
WHERE StartTime < DATEADD(dd,-30,GETDATE())

Output File Cleanup

Elimina los archivos de logs generados por cada ejecución de los jobs más antiguos de 30 días.

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m
*_*_*_*.txt /d -30 2^>^&1') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del
"$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"

Sp_purge_jobhistory

Elimina el historial de los jobs más antiguos a 30 dias.

DECLARE @CleanupDate datetime
SET @CleanupDate = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate

Respecto a los jobs de los backups las combinaciones son prácticamente infinitas, por lo que aquí os dejo el nombre de los jobs y el enlace a la web oficial para que podáis revisar algunas configuraciones.

• DatabaseBackup – SYSTEM_DATABASES – FULL
• DatabaseIntegrityCheck – USER_DATABASES
• DatabaseBackup – USER_DATABASES – FULL
• DatabaseBackup – USER_DATABASES – DIFF
• DatabaseBackup – USER_DATABASES – LOG

Aquí podréis encontrar las configuraciones de todos los jobs mencionados en este post así como cualquier otra información necesaria de los Ola Hallengren https://ola.hallengren.com/sql-server-backup.html

Espero que este post haya podido ayudarte en algo, si no es así, pídele ayuda a tu senior.