Introducción
La base de datos SSISDB (también conocida como el catálogo de Integration Services) se introdujo en SQL Server 2012 para eliminar el desorden de la base de datos MSDB y proporcionar una infraestructura interna de registro e informes. En pocas palabras, SSISDB es un marco SSIS que hace que SQL Server Integration Services sea más robusto y amigable para la empresa al proporcionar:
- Copia de seguridad de la base de datos
- Encriptación de la base de datos
- Soporte de entornos
- Soporte de parámetros de proyectos y paquetes
- Package versionado
- Informes de rendimiento de SSRS del cliente incorporados en SSMS
- Despliegue directamente desde SSDT
Mientras que el marco SSISDB hizo que SSIS fuera mucho más capaz, vino con algunas suposiciones de Microsoft (a.k.a. valores por defecto). Esos valores predeterminados están ahí para ayudarle a mantenerse en pie, pero puede que no sean óptimos cuando empiece a correr, y están lejos de ser perfectos cuando esté esprintando.
Este artículo aborda los valores predeterminados, por qué esos valores predeterminados pueden no ser óptimos, y cómo cambiar esos valores predeterminados.
Problema
El catálogo de SSISDB viene con un proceso incorporado para limpiar las operaciones y el versionado del proyecto. Este proceso de limpieza se basa en los valores predeterminados de SSISDB que podrían hacer que su SSIS sea inoperable si no se cambia.
Por lo general, cada nuevo proyecto de SSIS comienza con la construcción y ejecución de sólo unos pocos paquetes mientras hay una abundancia de espacio libre en el disco. Avance rápido de 3 a 6 meses y usted tiene un número de proyectos SSIS, decenas o cientos de paquetes, y también podría tener una necesidad de datos frescos para la presentación de informes y análisis (léase: ejecutar constantemente paquetes durante todo el día). Esos paquetes SSIS pueden acumular datos de versiones (menos que un problema) y datos de registro (más que un problema) más allá de los valores predeterminados de limpieza de Microsoft, lo que puede llevar a que el tamaño de la base de datos SSISDB se dispare y crezca inesperadamente (léase: que se coma todo el espacio disponible en el disco).
El único valor predeterminado que es responsable de capturar los datos relacionados con el tiempo de ejecución de SSIS es Server-wide Default Logging Level = Basic. Esta configuración es, en última instancia, la que gestiona el tamaño de la base de datos SSISDB y, en última instancia, ocupa el espacio en disco. Dicho esto, el Catálogo SSIS (Fig 1) provee otras opciones para controlar el tamaño de la base de datos SSISDB/espacio de disco:
- Nivel de Registro Predeterminado en todo el Servidor
- Limpieza por defecto:
- Limpiar registros periódicamente = Verdadero
- Eliminar periódicamente versiones antiguas = Verdadero
- Periodo de retención (días) = 365
- Número máximo de versiones por proyectos = 10
Mientras que los cambios en el nivel de registro por defecto en todo el servidor de Basic a None para recuperar espacio en el disco es muy poco práctico y hará que la solución de problemas sea una pesadilla, los cambios en otras configuraciones del Catálogo SSISDB como el Número Máximo de Versiones por Proyectos y el Período de Retención (días) pueden tener un efecto mínimo en la resolución de problemas, mientras se mantiene el control de la base de datos/espacio en disco de SSISDB.
Todos esos valores predeterminados están impulsando el proceso de limpieza (también conocido como trabajo de mantenimiento del servidor SSIS). Mientras que tanto Clean Logs Periodically = True como Periodically Remove Old Versions = True son prácticamente siempre perfectos, los ajustes tanto para Maximum Number of Versions per Projects = 10 como para Retention Period (days) = 365 podrían ser demasiado optimistas. Y aquí está el por qué.
La razón principal por la que ambos valores por defecto Clean Logs Periodically = True y Periodically Remove Old Versions = True son perfectos es que, sin esos valores por defecto (o efectivamente si ambos se cambian a False), SSISDB crecerá indefinidamente ya que sólo acumulará datos de registro y versiones sin eliminar ningún dato y se quedará sin espacio en disco.
La razón principal por la que tanto el Número Máximo de Versiones por Proyectos = 10 como la Retención de datos de registro durante 365 días podrían ser demasiado optimistas es la decisión de Microsoft de retener los datos basándose en un periodo de tiempo y no en el tamaño. Esto significa que el número de período fijo no tiene en cuenta la cantidad de datos que se acumularán. Mientras que el Número Máximo de Versiones por Proyectos podría convertirse en un problema una vez que tengas miles de paquetes, el Periodo de Retención (días) se va a convertir en un gran problema una vez que tengas paquetes que se ejecuten cada pocos minutos. No creo que Microsoft esperara nunca la acumulación de datos de registro para 1440 ejecuciones al día (que se ejecutan cada minuto) lo que requerirá una limpieza más a menudo que cada 365 días o se quedará sin espacio en disco.
Solución
La solución implicaría cambiar los valores por defecto a números más pequeños, para que SSISDB guarde menos información. Basándome en mi experiencia personal de ejecutar paquetes cada minuto (requisito de la plataforma de comercio electrónico) y tener hasta 10 proyectos, disminuir el Periodo de Retención (días) de 365 (por defecto) a 7 días y el Número Máximo de Versiones por Proyectos de 10 (por defecto) a 5 sería suficiente para controlar SSISDB/espacio en disco y a la vez mantener suficiente información para depurar y solucionar problemas.
Tiene dos opciones para realizar este cambio:
- Cambie los valores predeterminados utilizando SQL Server Management Studio (también conocido como «SSMS») expandiendo Integration Services, haciendo clic con el botón derecho en SSISDB y luego haciendo clic en el elemento de menú Properties (Fig 2 – SSMS).
- Cambie los valores predeterminados utilizando T-SQL a continuación para actualizar los registros en la tabla ssisdb.catalog.catalog_properties directamente.
| Código fuente |    |
UPDATE ssisdb.CATALOG.catalog_propertiesSET property_value = 5WHERE property_name = 'MAX_PROJECT_VERSIONS' UPDATE ssisdb.CATALOG.catalog_propertiesSET property_value = 7WHERE property_name = 'RETENTION_WINDOW'
Una vez completado el cambio, debería poder confirmar los cambios ejecutando la siguiente consulta T-SQL (Fig 3 – Catálogo SSISDB)
| Código fuente | |
SELECT * FROM ssisdb.CATALOG.catalog_properties
Aunque el cambio de los valores predeterminados puede solucionar el problema en el futuro, puede que no solucione el problema de espacio en disco de SSISDB existente con la suficiente rapidez. Puede acelerar el proceso de limpieza modificando el código y cambiando @delete_batch_size de 1000 a como 10000 alterando el procedimiento almacenado ssisdb.internal.cleanup_server_retention_window (Fig 4 – Procedimiento de retención).
Resumen
Este artículo destacó un problema potencial con los valores predeterminados del Catálogo de SSISDB que podría llevar a quedarse sin espacio en el disco y proporcionó una solución que obligaría a SSISDB a retener menos información.