SSISDB is too big and it is not every time the retention period’s fault!

In all blogs that I read, the solution is just to change the retention period to a reasonable value.
Yes, it is true but in some case you need to go deeper in the analysis…
Like you, I changed the retention period, ran the cleanup but finally, I had always a big database… Why, Why, Why?
My first step for this analysis, is to see in the SSISDB, the Disk Usage by Table Reports from SSMS.

I see that table internal.execution_component_phases is the biggest.
I select in this table what are the packages and how many entries per package I have in this table…
Query used:

select distinct package_name, count(*) as number_of_entry from internal.execution_component_phases group by package_name

In my case, the “Package1.dtsx” has 3756828 entries.
I go deeper with this package and search how many entries I have with my package per execution.
Query used:

select distinct execution_id, count(*) from internal.execution_component_phases where package_name='package1.dtsx' group by execution_id

It is for each execution almost the same.
I guess it comes from an execution parameter.
I select all parameters from one execution (id=719).
Query used:

SELECT * FROM catalog.execution_parameter_values where execution_id = 719

I see in my result, that the LOGGING_LEVEL is set to 2 and not 1 the default value…

To confirm my theory, I check this parameter for all executions.
Query used:

SELECT distinct ecp.execution_id, epv.parameter_name,epv.parameter_value
FROM catalog.execution_parameter_values epv inner join internal.execution_component_phases ecp
on ecp.execution_id=epv.execution_id where epv.parameter_name='LOGGING_LEVEL'

And as you can see, the LOGGING_LEVEL is always set to 2.

What is the signification of a LOGGING_LEVEL=2?
I go directly to the msdn to read the logging level on “Enable Logging for Package Execution on the SSIS Server”
In the documentation, you can read that the Logging Level “Performance” is linked to the catalog.execution_component_phases and  the start and end times for the data flow components are logged for each phase of an execution. But this view is also available for the logging level “Verbose”…
In this webpage, I haven’t the match between the logging level and its number.
I find the match in the webpage from the “catalog.set_execution_parameter_value” and see that the 2 is the “Performance” Logging level.

Conclusion: I contacted the package owner and explained him the case. I asked him why this level of logging and he said me that he thought that was to have performance… He didn’t read correctly the documentation!
After the explanation of the logging level set to performance, he changed the logging level to Basic and the SSISDB grew normally again. 😉