SSIS Catalog Health

Overview

Why is SSISDB the largest database on this instance, and is the thing that is supposed to hold it back still keeping up?

Retention defaults to 365 days. Logging defaults to Basic. A catalog switched to Verbose for one investigation and left there will quietly outgrow every user database on the instance over a few months, and the cleanup meant to contain it is one nightly single threaded delete that can take longer than a day to do a day of work once it is behind.

Where to find it

Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Catalog Health.

The three things, side by side

What is in the internal tables, by space

Six tables account for essentially all of an oversized SSISDB, and the largest one is almost always internal.event_message_context.

The four settings that decide it

Setting What it does
RETENTION_WINDOW How many days of operations are kept. Default 365.
OPERATION_CLEANUP_ENABLED Whether the nightly cleanup runs at all.
MAX_PROJECT_VERSIONS How long each project’s version chain is.
VERSION_CLEANUP_ENABLED Whether the version chains are trimmed.

The maintenance job’s recent runs

A cleanup whose duration climbs run to run is a cleanup that is losing, and that trend is the finding rather than any single number.

Reading the grid

Column What it is
Table The internal table.
Rows Row count.
Reserved Everything it has taken from the file.
Data The rows on disk.
Index What the indexes hold.
Share Reserved as a share of SSISDB.
What it holds One sentence saying what writes into this table and at which logging level.

The toolbar

Control What it does
Settings and cleanup The four settings and the maintenance job’s recent runs.
Retention script Copies a stepped retention script.
Recent runs Opens SSIS Packages Run.

Nothing here changes anything, and the script steps down

The retention script is a script, in a window, and it steps the window down rather than dropping it in one go.

Going from 365 days to 45 in a single statement is a cascading delete that can run for hours and fill the log, and SSISDB is in FULL recovery by default.

So the script walks the window down in stages, with each stage a statement somebody runs, reads the result of, and then runs the next.

Where the data comes from

Source What it gives
sys.dm_db_partition_stats Rows and space for the internal tables.
catalog.catalog_properties RETENTION_WINDOW, OPERATION_CLEANUP_ENABLED, MAX_PROJECT_VERSIONS, VERSION_CLEANUP_ENABLED.
msdb.dbo.sysjobs, dbo.sysjobhistory The SSIS Server Maintenance Job and how long its recent runs took.
sys.databases SSISDB’s recovery model, for the caveat about the log.
Report Why you would go there
SSIS Who Fills It Which package is writing all of it. This page says which table; that one says which package.
SSIS Overrides Where a logging level was raised for an investigation and never put back.
SSIS Data Flow What Verbose logging is buying you while it fills the database.
SSIS Catalog Inventory The version chains MAX_PROJECT_VERSIONS bounds.
Job Step Failures The maintenance job, when it is failing rather than merely slow.

Frequently asked questions

event_message_context is enormous. Something is logging at Verbose. SSIS Who Fills It names the package, and dropping that one package back to Basic takes a minute.

The cleanup job takes longer every night. It is losing. Once a nightly cleanup takes longer than a day to remove a day of data, the backlog grows on its own and stepping the retention window down is the only way out.

Can I just set retention to 45 days? You can, and on a catalog that has been at 365 days for two years that single statement is a cascading delete that can run for hours with SSISDB in FULL recovery. That is why the script steps down instead.

Is cleanup enabled by default? Yes, but it is one of the four settings on this page precisely because it can be turned off and forgotten.