{"id":441,"date":"2014-09-04T14:35:50","date_gmt":"2014-09-04T19:35:50","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=441"},"modified":"2014-09-04T14:35:50","modified_gmt":"2014-09-04T19:35:50","slug":"managing-the-size-of-the-sql-server-ssis-catalog-database","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/managing-the-size-of-the-sql-server-ssis-catalog-database\/","title":{"rendered":"Managing the size of the SQL Server SSIS catalog database"},"content":{"rendered":"

The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you\u2019ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created. The scope of this post will focus on the SSISDB growth more than the SSIS catalog itself.<\/p>\n

SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.<\/p>\n

Also included when you enable this feature is a SQL Server Agent Job called SSIS Server Maintenance job:<\/p>\n

\"SQL<\/a>
\nInside this job are two steps, SSIS Server Operation Records Maintenance and SSIS Server Max Version Per Project Maintenance, that will help clean up the database. By default, this job is set to run at 12:00am nightly which is sufficient:<\/p>\n

\"SQL<\/a>
\nLooking at the first step, SSIS Server Operations Records Maintenance, you will notice that it executes a stored procedure named internal.cleanup_server_retention_window. This sounds like it could be the stored procedure that cleans up history:<\/p>\n

\"SQL<\/a>
\nLet\u2019s browse out to the stored procedure in Management Studio and take a look at the code:<\/p>\n

\"SQL<\/a>
\nYou can see from the very beginning of the stored procedure in the BEGIN TRY statement it first looks to see if Operation cleanup is enabled and if cleanup is enabled then it looks for the Retention Window:<\/p>\n

\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you\u2019ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created. The scope of this post will focus on the SSISDB growth more than the SSIS catalog itself. SSIS projects, packages, parameters, environments, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49,91,25],"tags":[105,60,50],"class_list":["post-441","post","type-post","status-publish","format-standard","hentry","category-integration-services-ssis","category-maintenance","category-monitoring","tag-monitoring","tag-sql-server-integration-services","tag-ssis"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=441"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/441\/revisions"}],"predecessor-version":[{"id":450,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/441\/revisions\/450"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}