none
SSIS Server Maintenance Job fills SSISDB log file

    Question

  • We have SSIS - (Version 2012)

    As part of this we have the SSIS Server Maintenance Job - i.e. this job is a SQL agent job - that should run daily to housekeep the SSISDB.

    At some point this job was disabled.

    When we run it - it just causes the Log file for the SSISDB to grow rapidly - until it runs out of space - and the job crashes.

    The SSISDB is approx 25GB - i.e. that the size of the .mdf file

    The SSISDB.ldf  starts at approx 50MB - i.e. very small - and after about 15 mins has grown to 20GB (thats all the space allocated on the drive for it) - so the job crashes.

    Any ideas ?

    We are running the following SQL server version:

    Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) 


    • Edited by goofygdog2 Monday, February 11, 2019 5:43 PM
    Monday, February 11, 2019 5:41 PM

All replies

  • Hello, 

    You may need to change the value of property RETENTION_WINDOW . 

    Please check this link:

    Managing the size of the SQL Server SSIS catalog database


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 12, 2019 9:12 AM
  • Thanks - we are aware of these settings.

    However, this does not help, as when we run the SSIS maintenance job, the database log file grows exponentially and the job crashes.

    Tuesday, February 12, 2019 9:28 AM
  • Hello,

    Then you can try to shrink SSISDB, please check the steps in Shrinking the SSISDB

    Before you change anything in SSISDB, make a backup


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 12, 2019 10:04 AM
  • To minimize logging, do you have database recovery model set to SIMPLE for SSISDB?

    Are you performing regular backups of SSISDB to help control tranlog size?

    Have you tried customizing SSISDB Logging level or Retention Period to help minimize logging?

    Reference:
    SQL Server Integration Services Catalog Best Practices

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, February 12, 2019 11:06 PM
    Tuesday, February 12, 2019 10:56 PM