Purger la base SSISDB

Purger la base SSISDB

Lorsque nous installons un catalogue SSIS sur nos instances, SSIS crée sa propre base de données SSISDB. Elle contient notamment les logs d’exécution de tous les packages. Après plusieurs mois d’activité, on peut constater que cette base peut grossir énormément.

Configurer la rétention

Dans SSMS, on peut configurer une période de rétention dans les propriétés d’un catalogue SSIS.

retention01

Il faut préciser le nombre de jours dans « Retention Period (days) » et s’assurer que « Clean Logs Periodically ». Par défaut, le nombre de jours est fixé à 365.

retention02

Le Job « SSIS Server Maintenance Job » utilise cette configuration pour purger la base SSISDB régulièrement. C’est la procédure stockée internal.cleanup_server_retention_window qui s’occupe de faire le ménage.

retention03

Script Maison

Voici un script qui permet de purger la base SSISDB en précisant un nombre de jours de rétention. Oui c’est toujours plus marrant de le faire soit même mais ce n’est surement pas supporté par Microsoft 🙂

USE SSISDB;
SET NOCOUNT ON;

DECLARE @NbJourRetention int = 90;

IF object_id('tempdb..#OperationASupprimer') IS NOT NULL
BEGIN
DROP TABLE #OperationASupprimer;
END;

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

INSERT INTO
#OperationASupprimer
(
operation_id
)
SELECT
IO.operation_id
FROM
internal.operations AS IO
WHERE
IO.start_time < DATEADD(day, -@NbJourRetention, GETDATE());

DELETE T
FROM internal.event_message_context AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.event_messages AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.operation_messages AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.operations AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

Dans le script ci-dessus, la base ne contiendra plus que les 90 derniers jours de log. Il suffit de changer la valeur de la variable @NbJourRetention pour une période de rétention plus ou moins importante.

Si vous n’avez jamais purgé la base SSISDB, exécutez cette requête en diminuant progressivement le nombre de jours de rétention et en sauvegardant le journal de log de la base. Sinon celui ci pourrait grossir d’un seul coup !

Il ne vous reste plus qu’à planifier ce job régulièrement. 1 fois par semaine dans une période creuse par exemple.