Sql 2014 "SSISDB" maintenance plan performance issues?

  • We are new to using SSISDB. From the following URL (and others) there seems to be some issue associated with that database and maintenance plans cleanup. Have these been corrected in Sql 2014 or Sql 2016? Are there other known issues associated with this rather poorly documented feature?

    TIA,

    bd

    From http://www.ssistalk.com/2012/07/16/ssis-2012-beware-the-ssis-server-maintenance-job/

    "

    The entire SSISDB catalog is linked together via foreign keys, most of which are all linked to a single ancestor table – internal.operations....

    That’s just one downstream table! This entire process ("SQL Server Maintenance Job") completely, utterly locks up the SSISDB database from any SELECT/INSERT data....

    With that said, what does it mean to you? Potentially the cancellation of other SSIS packages that are running at the same time. For us, each and every time the maintenance job runs, packages get canceled. This is not good.

    "

  • I haven't got any experience of this in 2014 or 2016, but in 2012 I experienced a lot of issues if the amount of data in the SSISDB was fairly large. To combat this I reduced the default values for how long information (365 days) and project versions (10 I believe) are retained for.

    At least this way the amount of data to be "maintained" by that job is kept to a minimum.

  • SSC -

    I'm new to SSIS 2014. You wrote

    >>> To combat this I reduced the default values for how long information (365 days) and project versions (10 I believe) are retained for.

    Where are these settings found?

    bd

  • From management studio

    Navigate to Integration Services Catalog

    Right-click SSISDB

    Properties

    In the section of Operations Log: Retention Period

    In the section of Project Versions: Maximum number of versions per Project

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply