Introduction

This blog post is an improvement on my previous blog post found here – https://datasteve.com/2019/12/06/ssisdb-catalog-defaults-best-practices/ While my original blog post assumed a problem, this time we will make sure there is a problem before providing a solution. As a bonus, I’m also going to provide a custom advisory condition for your existing SentryOne SQLSentry environment.

Problem

To reiterate the potential problem. SSISDB is shipped with retention period of 365 days. While it might be OK in some cases like SSIS packages that are scheduled to run once a day, it would a bad idea if those packages are running more often, like every 15 minutes. Running any package every 15 minutes would result into 1440 (24x4x15) records (at the very minimum of one execution component in a package) that are inserted into [internal].[executions]. Suffice to assume, that would not end up well and will result in ballooning of SSISDB database which might lead to a drastic increase in disk space.

Solution

The following code would try to identify if the problem is there by looking for jobs that are executed “too often” and check for retention window. If we have at least one job that is running frequently and SSISDB has a retention window of 365, only when we have a real problem and get alert. 

IF EXISTS
-- print 'check job frequency'
(
select 1
--jobs.name
from msdb.dbo.sysjobs as jobs
inner join msdb.dbo.sysjobsteps as jobeps on jobeps.job_id = jobs.job_id
inner join msdb.dbo.sysjobschedules as jobles on jobs.job_id = jobles.job_id
inner join msdb.dbo.sysschedules as les on les.schedule_id = jobles.schedule_id
where 1=1
AND jobs.enabled =1
AND les.enabled =1
and les.freq_type = 4 -- 4 = Daily
and les.freq_interval = 1
and les.freq_subday_type IN (2, 4, 8) -- 2 = Seconds, 4 = Minutes, 8 = Hours
and les.freq_subday_interval > 0
and jobeps.subsystem = 'SSIS'
)
AND EXISTS(SELECT 1 FROM master.sys.databases WHERE name = 'SSISDB')
AND EXISTS
(
--print 'check ssis retention'
select 1 from ssisdb.catalog.catalog_properties where 1=1 and property_name = 'RETENTION_WINDOW' and property_value > 30 --'365'
)
SELECT 1 --log retention window is too high
ELSE SELECT 0 -- nothing to see, all good

Just in case that you are running SentryOne SQLSentry, here is a custom condition to download from my GitHub public repo (locate Raw button, press “Alt” on your keyboard and left-click on your mouse at the same time to download). Once its imported, SQLSentry would constantly check on that problem and alert on it.

Disclaimer

This blog post is partially based on the following resources: https://www.timmitchell.net/post/2017/03/31/ssis-catalog-logging-tables/

https://stackoverflow.com/questions/4604663/download-single-files-from-github