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.

5 thoughts on “Purger la base SSISDB

    • Je ne pense pas. Je sais juste que quand tu arrives sur un catalogue SSIS qui est énormément sollicité avec un historique très grand, la base peut être énorme et lente pour cracher le moindre rapport d’exécution et ce genre de script est bien utile 🙂
      Et comme tu me l’as suggéré sur Twitter, je vais me pencher sur la durée de rétention dans les properties du catalogue SSIS et je mettrai à jour l’article en conséquence.

  1. Salut Greg, ne manque-il pas la purge de execution_component_phases par exemple?

    • https://msdn.microsoft.com/en-us/library/hh230981.aspx
      On dirait bien que cette table peut également être purgée. A l’époque, je n’avais pris que les tables qui grossissaient dans ce script de purge, je n’avais peut être pas le bon niveau de logging… ? « Warning : The catalog.execution_component_phases view provides this information when the logging level of the package execution is set to Performance or Verbose. For more information, see Enable Logging for Package Execution on the SSIS Server. »

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.