Wenn man die Suchmaschine seines Vertrauens bemüht und nach "ssisdb size reduce" sucht, wird man schnell Tipps dazu finden was hier zu tun ist! In diesem Artikel will ich einen Blick auf die wirklichen Hintergründe werfen.

Warum ist die SSISDB so groß? - 1. Versuch

Antwort: Du musst die alten Daten löschen und die Tage der Protokollierung reduzieren!
Okay, das macht zu einem gewissen Grad Sinn. Schnell wird auch über den Löschalgorithmus des SQL Servers geschimpft, der hier nicht wirklich optimal vorgeht und bei sehr großen Datenmengen schon mal an seine Grenzen kommt. Größen der SSISDB von mehreren 100 GB scheinen durchaus üblich zu sein.

Aber zum Glück gibt es ja alternative Löschskripte, die jeder auf eigenes Risiko ausprobieren kann. Z. B. dass von Tim Mitchell: A Better Way to Clean Up the SSIS Catalog Database

Von Tim gibt es auch einen Artikel über die Mechanismen, die dazu geführt haben und wie man die Default-Einstellung von 365-Tagen reduziert.

Aber ist das wirklich die Antwort auf die Frage: Warum ist meine SSISDB so groß?

Warum ist die SSISDB so groß? - 2. Versuch

Antwort: Weil Du so viel da rein schreibst!
Frage: Das mache ja nicht ich, sondern der SQL Server. Kann ich das denn beeinflussen?
Antwort: Ja, beim "Integration Services Catalog" gibt es eine Einstellung für die Standard-Einstellung des Logging-Levels!

Frage: Ich habe gesehen, dass steht ja schon auf Basic, was kann ich denn da noch ändern?
Antwort: Basic heißt nicht minimales Logging, sondern hier werden fast alle Informationen protokolliert. Versuch es doch mal mit Performance. Dann bekommst Du nur noch Warnungen, Fehler und Änderungen von Variablen mit. Der Überblick in den Reports, die das Management Studio zur Verfügung stellt bleibt Dir erhalten! Man kann dies auch per T-SQL umstellen:

EXEC [SSISDB].[catalog].[Configure_catalog]
  @property_name=N'SERVER_CUSTOMIZED_LOGGING_LEVEL',
  @property_value=N''

EXEC [SSISDB].[catalog].[Configure_catalog]
  @property_name=N'SERVER_LOGGING_LEVEL',
  @property_value=2

go 

Frage: Ich habe die Einstellung auf Performance geändert, es wird aber immer noch so viel protokolliert. Was mache ich falsch?
Antwort: Dieser Default-Wert zieht nur bei neuen Jobs, die einen SSIS-Jobstep bekommen. Die alten Jobs müssen manuell angepasst werden.
Frage: Wie geht das? Ich habe noch nie gesehen, wo man das einstellt!
Antwort: Dazu muss man in den Jobstep gehen und dann sind es nur noch wenige Klicks (also nicht so, dass man direkt drüber stolpern würde)

Zuerst geht man also in den Job-Step, dann (1) dort in die "Configuration". Die dritte Lasche dort hat die Bezeichnung "Advanced" (2) und unter "Logging Level" (3) kann man dann diese erweiterte Einstellung vornehmen. Falls man den Job mal skripten lässt, findet man jetzt auch diesen Text im Skript: /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2.

Frage: Lohnt es sich denn das umzustellen?
Antwort: Es kann durchaus sein, dass die reduzierte Protokollierung nur noch ein Tausendstel der alten Protokollierung ausmacht. Man könnte dann also auch wieder den Zeitraum der Protokollierung auf größere Werte setzen, die vorher undenkbar waren. Bei sehr intensiver Protokollierung kann es auch durchaus einen messbaren Perfomance-Gewinn geben, wenn hier nicht hunderttausende überflüssige Meldungen protokolliert werden. Für einzelne Ausführungen kann man ja jederzeit das Logging-Level wieder auf andere Werte setzen, wenn man z. B. einzelne Datenflüsse im Detail nachvollziehen will.

Frage: Kann ich mir auch mal den Hintergrund dazu in den Tabellen anzeigen lassen?
Antwort: Ja, hier sind ein paar Statements, die helfen können den Überblick zu bewahren und mit denen man nachvollziehen kann, was noch so protokolliert wird.

-- Was wurde heute protokolliert?
SELECT operation_id,
       e.package_name,
       Count(*) AS Anzahl
FROM   [SSISDB].[internal].[operation_messages] m
       INNER JOIN [SSISDB].[internal].[executions] e
               ON m.operation_id = e.execution_id
WHERE  [message_time] > Cast(Getdate() AS DATE)
GROUP  BY operation_id,
          e.package_name
ORDER  BY operation_id;

-- Was wurde in der Vergangenheit so alles protokolliert?
SELECT Cast([message_time] AS DATE) My_Message_Time,
       Count(*)                     AS Anzahl
FROM   [SSISDB].[internal].[operation_messages]
GROUP  BY Cast([message_time] AS DATE)
ORDER  BY my_message_time;

-- Wieviel Ausführungsstatistiken wurden für heute protokolliert?
SELECT e.package_name,
       m.[execution_id],
       Count(*)
FROM   [SSISDB].[internal].[executable_statistics] m
       INNER JOIN [SSISDB].[internal].[executions] e
               ON m.execution_id = e.execution_id
WHERE  start_time > Cast(Getdate() AS DATE)
GROUP  BY e.package_name,
          m.[execution_id];

-- Overview für eine einzelne Ausführung
DECLARE @execution_id INT = 18903; -- Hier die Execution-ID einsetzen
SELECT CASE s.execution_result
         WHEN 0 THEN 'Success'
         WHEN 1 THEN 'Failure'
         WHEN 2 THEN 'Completion'
         WHEN 3 THEN 'Cancelled'
       END                           AS Result,
       s.execution_duration / 1000.0 AS duration,
       e.package_name,
       e.executable_name,
       s.execution_path
FROM   [SSISDB].catalog.[executable_statistics] s
       INNER JOIN [SSISDB].catalog.[executables] e
               ON s.executable_id = e.executable_id
                  AND s.execution_id = e.execution_id
WHERE  start_time > Cast(Getdate() AS DATE)
       AND s.execution_id = @execution_id
-- ORDER BY e.package_name, s.execution_path      -- Reihenfolge im SSISDB Report
ORDER  BY s.start_time; 

Frage: Warum hat mir das vorher keiner erzählt?