¿Cómo puedo limpiar SSISDB?

Solución:

Phil Brammer se encontró con esto y una serie de otras cosas relacionadas con el cuidado y la alimentación del catálogo SSIS, que cubre en su publicación Catalog Indexing Recommendations.

Problema de raíz

La raíz del problema es que MS intentó diseñar el SSIS con RI en mente, pero fueron perezosos y permitieron que ocurrieran las eliminaciones en cascada en lugar de manejarlas explícitamente.

Fuera de la caja, la nueva base de datos de catálogo SSIS 2012 (SSISDB) tiene aplicada alguna indexación básica, con integridad referencial configurada para realizar eliminaciones en cascada entre la mayoría de las tablas.

Ingrese el trabajo del Agente SQL, “Trabajo de mantenimiento del servidor SSIS”. Este trabajo está configurado de forma predeterminada para ejecutarse a la medianoche todos los días y utiliza dos parámetros de catálogo para funcionar: “Limpiar registros periódicamente” y “Período de retención (días)”. Cuando se establecen, el trabajo de mantenimiento depura cualquier dato fuera del período de retención indicado.

Este trabajo de mantenimiento elimina 10 registros a la vez en un bucle de operaciones internas y luego se distribuye en cascada en muchas tablas posteriores. En nuestro caso, tenemos alrededor de 3000 registros de operaciones para eliminar diariamente (¡10 a la vez!) Que se traduce en 1,6 millones de filas de internal.operation_messages. ¡Esa es solo una mesa aguas abajo! Todo este proceso bloquea completamente la base de datos SSISDB de cualquier dato SELECT / INSERT

Resolución

Hasta que MS cambie la forma en que funcionan las cosas, la opción admitida es

mover el programa de trabajo de mantenimiento a un momento más apropiado para su entorno

Sé que en mi cliente actual, solo cargamos datos en las horas de la madrugada, por lo que SSISDB está en silencio durante el horario comercial.

Si ejecutar el trabajo de mantenimiento durante un período de inactividad no es una opción, entonces está buscando crear sus propias declaraciones de eliminación para tratar de obtener las eliminaciones en cascada para chupar menos.

En mi cliente actual, hemos estado ejecutando alrededor de 200 paquetes por noche durante los últimos 10 meses y también tenemos 365 días de historial. Nuestras tablas más grandes, en un orden de magnitud, son.

Schema    Table                   RowCount
internal  event_message_context   1,869,028
internal  operation_messages      1,500,811
internal  event_messages          1,500,803

El conductor de todos esos datos, internal.operations solo tiene 3300 filas, lo que se alinea con el comentario de Phil sobre cuán exponencialmente crecen estos datos.

Entonces, identifique el operation_id para ser purgado y la eliminación de las tablas hoja trabajando de regreso al núcleo, internal.operations mesa.

USE SSISDB;
SET NOCOUNT ON;
IF object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
BEGIN
    DROP TABLE #DELETE_CANDIDATES;
END;

CREATE TABLE #DELETE_CANDIDATES
(
    operation_id bigint NOT NULL PRIMARY KEY
);

DECLARE @DaysRetention int = 100;
INSERT INTO
    #DELETE_CANDIDATES
(
    operation_id
)
SELECT
    IO.operation_id
FROM
    internal.operations AS IO
WHERE
    IO.start_time < DATEADD(day, [email protected], CURRENT_TIMESTAMP);

DELETE T
FROM
    internal.event_message_context AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.event_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.operation_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

-- etc
-- Finally, remove the entry from operations

DELETE T
FROM
    internal.operations AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

Se aplican las advertencias habituales

• no confíe en el código de randoms en Internet
• use los diagramas de ssistalk y / o tablas del sistema para identificar todas las dependencias
• es posible que solo deba segmentar sus operaciones de eliminación en operaciones más pequeñas
• puede beneficiarse descartando RI para las operaciones, pero asegúrese de volver a habilitarlas con la opción de verificación para que sean confiables.
• consulte con su dba si las operaciones duran más de 4 horas

Julio de 2020 editar

Tim Mitchell tiene un buen conjunto de artículos sobre limpieza automática del catálogo SSIS y una mejor manera de limpiar la base de datos del catálogo SSIS y su nuevo libro elegante El catálogo SSIS: instalar, administrar, proteger y monitorear su infraestructura ETL empresarial

@Yong Jun Kim señaló en los comentarios

Existe la posibilidad de que SSIS DB tenga diferentes nombres de tabla con escalado horizontal al final ahora. En lugar de internal.event_message_context, puede ser internal.event_message_context_scaleout. En lugar de internal.operations_messages, puede ser internal.operations_messages_scaleout. Simplemente modifique los nombres de las tablas en el código en consecuencia, y debería funcionar bien

Este es ciertamente el caso si está utilizando un SSIS IR dentro de Azure Data Factory. Encontrará las tablas “normales” todavía presentes pero vacías, con el *_scaleout versiones que contienen todos los datos.

Referencias

• Recomendaciones de indexación de catálogos
• Tenga cuidado con el trabajo de mantenimiento del servidor SSIS
• Rendimiento lento cuando ejecuta el trabajo de mantenimiento del servidor SSIS para eliminar datos antiguos en SQL Server 2012

---

---

Utiliza Nuestro Buscador