Infrastructure at your Service

Stéphane Haby

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.
SSISDB_Size_01
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

SSISDB_Size_02

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

SSISDB_Size_03

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

SSISDB_Size_04

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'

SSISDB_Size_05

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. ;-)

 

2 Comments

  • ijaz says:

    Hi,
    Can you pls help me on how to clear these messages. New to SQL server.

    Table Name # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB)
    internal.event_messages 20,395,486 12,961,648 12,453,488 506,104 2,056
    internal.operation_messages 20,395,487 6,013,488 5,538,872 473,456 1,160
    internal.event_message_context 17,161,866 5,680,320 5,137,744 541,384 1,192
    internal.executable_statistics 700,174 169,096 152,128 16,440 528
    internal.execution_parameter_values 502,407 133,136 120,920 11,768 448
    internal.object_versions 29 7,984 7,952 8 24
    internal.operations 26,499 7,192 5,896 720 576
    internal.operation_permissions 77,361 5,792 2,784 2,344 664
    internal.executions 25,755 5,568 5,000 72 496
    internal.operation_os_sys_info 25,755 2,656 1,576 624 456
    internal.object_parameters 1,706 816 400 144 272
    internal.executables 510 408 216 32 160
    internal.projects 6 48 8 40 0
    internal.packages 91 48 16 32 0
    internal.environments 3 32 8 24 0
    internal.environment_variables 20 32 8 24 0
    internal.folders 5 32 8 24 0
    internal.data_type_mapping 58 32 8 24 0
    internal.project_permissions 24 32 8 24 0
    internal.environment_permissions 9 32 8 24 0
    internal.catalog_properties 9 16 8 8 0
    internal.catalog_encryption_keys 6 16 8 8 0
    internal.environment_references 4 16 8 8 0
    internal.extended_operation_info 0 0 0 0 0
    internal.execution_data_statistics 0 0 0 0 0
    internal.execution_component_phases 0 0 0 0 0
    internal.execution_data_taps 0 0 0 0 0
    internal.folder_permissions 0 0 0 0 0
    internal.execution_property_override_values 0 0 0 0 0
    internal.validations 0 0 0 0 0

     

Leave a Reply


eight × 9 =

Stéphane Haby
Stéphane Haby

Delivery Manager