If we’re running SQL Server Integration Services (SSIS) then it’s database is the wonderful place where we store our packages ready for execution and the lovely logs we can interrogate when those packages inevitably break (of course not, our code is perfect, right?)
This database is all well and good in a fresh environment however when we’ve got a large number of packages executing multiple complex ETL processes, the size of the database can grow considerably. Fortunately as part of SSIS we get a handy ‘SSIS Server Maintenance Job’ created in the SQL agent which will run every night to look after the database. Well, sort of.
This job is used to prune down the amount of logs and legacy packages which we want to retain. If we pop open the Integration Services Catalog in SSMS and check its properties we’ll get to see the default settings which are used to drive the clean-up process each evening:
By default it’s set to retain an entire year of logs (yikes!) and 10 legacy versions of projects if you’ve had some hick-ups and need a roll back. You’ll also see the options to periodically remove the data based on each of these retention periods which default to being enabled. The maintenance job has two steps in it which are to check these parameters and instigate the clean-up if required.
In this instance we were looking at a test environment which had a full 12 months of data so the database had grown out to 150gb. Each evening the maintenance job runs and keeps this pruned down over a couple of minutes. However, reducing this retention by a couple of weeks and it was already exceeding 10 minutes – and taking a chunk out of the log file in the process.
Each environment will be different depending on how many packages you’ve got, how often they run and how much history you’ve already built up. This will drive how aggressive you want to be in pruning this size down, but like all bulk operations, being mindful of your log size and planning in advance how you want to manage that will be key.
There’s a great article by Tim Mitchell where he dives into an approach to clearing the database whilst avoiding issues with the cascading deletions which are used as part of the standard maintenance job.
Another approach you could take would be to reducing the retention period slowly over time to avoid any spikes in the log whilst still reducing the volumes to a more manageable level. If you’re looking for a way to keep the out of box process in place then you could consider adding an additional job or step to your agent to tick the retention down each day, for example:
DECLARE @NewRetention INT,
@DailyIncrement INT = -7,
@MinimumRetention INT = 28;
SELECT @NewRetention = property_value + @DailyIncrement
FROM [internal].[catalog_properties]
WHERE property_name = 'RETENTION_WINDOW';
IF @NewRetention < @MinimumRetention
SET @NewRetention = @MinimumRetention;
EXEC [catalog].[configure_catalog]
@property_name = 'RETENTION_WINDOW',
@property_value = @NewRetention;
With this in place we can keep the log at a reasonable size and use the agent to monitor the timings as we adjust the increment as needed to get things cleaned up. Once it’s down to the desired retention period then the job can simply be removed and the out of box processes will go back to maintaining the data volumes as normal.
Andy Brownsword 