SSIS Who Fills It

Overview

One package is writing most of this database. Which one?

SSIS Catalog Health says which table holds the space, which tells somebody the logging level is too high somewhere. This says which package, which tells them where.

Those are one click apart on the page and a fortnight apart in practice.

Where to find it

Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Who Fills It.

Why one package is usually the answer

A catalog that has grown to two hundred gigabytes almost never got there evenly.

It is usually one package left at Verbose after an investigation, or one looping package writing a thousand messages a run.

Dropping that one package back to Basic takes a minute, and it needs a name to be actionable at all.

This is the expensive query of the whole set

It aggregates the largest tables in the database with no useful time filter, because the accumulated total is the point: rows written last March are still taking up space.

So it has:

  • its own timeout
  • a sampled mode over a recent window
  • a header saying which of the two it used

The Everything and Last 7 days toolbar options are those two modes.

One finding worth expecting

The list will name packages that are no longer deployed.

Messages outlive the package that wrote them. That is not a defect, it is the cleanest possible win, since those rows can go with nothing to weigh up.

Reading the grid

Column What it is
Package The package that wrote the rows, including packages no longer deployed.
Messages Rows in internal.event_messages.
Context Rows in internal.event_message_context. Usually the largest column on the page.
Statistics Rows in internal.execution_data_statistics and the component phase tables.
Total rows The three added.
Share That total as a share of everything counted.
Runs How many executions contributed.
Per run Total rows divided by runs. A high per run figure is a logging level; a high total with a low per run is simply a package that runs constantly.
Highest level The highest logging level any of its runs used.
What to do Turn the level down, retire the package, or nothing.

The toolbar

Control Options Default
Scope Everything, Last 7 days Everything
Script to turn it down Copies the statements that set this package’s logging level back, without running them.
Catalog health Opens SSIS Catalog Health.

Where the data comes from

Source What it gives
internal.event_messages The message rows, by operation_id.
internal.event_message_context The context rows.
internal.execution_data_statistics, internal.execution_component_phases The statistics rows.
internal.executions The package name behind each operation.
Report Why you would go there
SSIS Catalog Health Which table holds the space, and whether the cleanup is keeping up.
SSIS Overrides Where the logging level was raised, and by whom.
SSIS Data Flow What the Verbose logging is buying you.
SSIS Catalog Inventory Whether the package writing all this is still deployed.

Frequently asked questions

A package on the list is not deployed any more. Then its rows can be removed with nothing to weigh up, which makes it the cheapest win on the page.

Everything mode takes a long time. It is the expensive query of the set, because the accumulated total is what matters and there is no useful time filter. Use Last 7 days when you want a quick answer about current behavior.

Per run is high but the total is small. The package logs heavily and rarely runs. Worth turning down, but it is not why SSISDB is large.

I turned the level down and nothing shrank. Turning it down stops new rows. The existing ones go when the retention window passes them, which is SSIS Catalog Health.