Swollen SSISDB caused by Continuous Deployment

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job.

The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps

SSIS Server Operation Records Maintenance

TSQL Script: EXEC [internal].[cleanup_server_retention_window]

SSIS Server Max Version Per Project Maintenance

TSQL Script: EXEC [internal].[cleanup_server_project_version]

The problem is we do Continuous Deployment (CD), we use Octopus Deploy to automate our deployment process – it’s so effective we often do multiple releases a day. This in turned caused the log file to swell when a large number of old projects are removed, which results in the drive filling – despite the recovery model being set to simple.

The solution was simple, add an additional log file, located on another drive (I used the backup drive as it has tons of free space), run the job, truncate the log files, then remove the extra log file. Then it was just a question of running the two cleanup stored procedures post deployment.