Managing the size of the SQL Server SSIS catalog database

By:   |   Comments (16)   |   Related: More > Database Administration


Problem

I have implemented the SSIS catalog that comes with SQL Server 2012 and I love the reporting and centralization that comes with it, however, I noticed after a few months that the database was growing exponentially. How can I keep the size of this database to a minimum?

Solution

The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created. The scope of this tip will focus on the SSISDB growth more than the SSIS catalog itself.

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.

Also included when you enable this feature is a SQL Server Agent Job called SSIS Server Maintenance job:

A SQL Server Agent job called SSIS Server Maintenance job.

Inside 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:

SSIS Server Operation Records Maintenance and SSIS Server Max Version Per Project Maintenance

Looking 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:

SSIS Server Operations Records Maintenance

Let’s browse out to the stored procedure in Management Studio and take a look at the code:

a stored procedure named internal.cleanup_server_retention_window

You 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:

Operation cleanup is enabled

You can also see that the stored procedure queries catalog.catalog_properties to find these values. Let’s take a look at catalog_properties:

Let’s take a look at catalog_properties

Operation_Cleanup_Enabled is set to TRUE meaning that the package will cleanup and Retention_Window is set to 365 meaning that the package will cleanup history, etc. if it’s older than 365 days. If we have a lot of packages or a lot of run times this will probably not be sufficient unless we have a need for 365 days of report history/execution history.

To change the retention value, first you need to determine how many days you would like to keep in history, then we can run the catalog.configure_catalog stored procedure to change this value. For my example, I’ll change to 150 because I don’t need to keep anything past 150 days:

Operation_Cleanup_Enabled is set to TRUE

For my example, my database size is 30001.81 MB with 9850.55 MB unallocated before changing this value:

My database size is 30001.81 MB with 9850.55 MB unallocated

After changing the value to RETENTION_WINDOW to 150 and running the SSIS Server Maintenance job package, my unallocated space jumped up to 15699.76 MB which reduced by database size by 5849.21 MB

Running the SSIS Server Maintenance job package

If we look at the second step in the job, SSIS Server Max Version per Project Maintenance, you will notice that it executes a stored procedure named internal.cleanup_server_project_version.

The SSIS Catalog keeps project versions so if you were to need to roll back to a previous version you would be able to. With that said, this sounds like it could be the stored procedure that cleans up how many project versions to keep:

SSIS Server Max Version per Project Maintenance

Let’s browse out to the stored procedure and take a look at the code:

The SSIS Catalog keeps project versions

You can see from the very beginning of the stored procedure in the BEGIN TRY statement it first looks to see if Version Cleanup is enabled and if cleanup is enabled then it looks for the Max Project Versions:

If Version Cleanup is enabled and if cleanup is enabled then it looks for the Max Project Versions

If we take a look at catalog.properties we can see that VERSION_CLEANUP_ENABLED is set to TRUE and MAX_PROJECT_VERSIONS is set to 10. For my example, I’ll leave this alone because I don’t think the number of versions kept in the database is hurting the size so much and I would like the ability to go back in case I need to troubleshoot a package:

VERSION_CLEANUP_ENABLED is set to TRUE and MAX_PROJECT_VERSIONS is set to 10
Next Steps
  • After changing the property value to 150 from 365, the SSIS Maintenance Job took over 8 hours to complete. This may or not happen to you depending on how large your database is and how much historical information is stored in it.
  • Check out more MSSQLTips.com SSIS tips here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, February 8, 2021 - 4:13:43 AM - Sane Lazo Back To Top (88196)
Very helpfull. Thank you very much!
God Bless You

Wednesday, July 11, 2018 - 3:56:41 PM - Breno O Nunes Back To Top (76613)

Hi Brady! Very useful TIP!!! Thanks a lot!

Tuesday, July 18, 2017 - 5:34:58 AM - Dvinge Back To Top (59542)

If your SSISDB are more or less heavily utilized you might consider running iterations taking 1 day at the time. Reason is that your events writing to the database while doing the delete will be hold by exclusive locks due to the pretty heavy cascade deleting operation .. your log might explode.

Suggestion could be something like

DECLARE @init as int = 365
DECLARE @target as int = 120

WHILE @init > @target
BEGIN

 EXEC catalog.configure_catalog 'RETENTION_WINDOW', @init;

 EXEC [internal].[cleanup_server_retention_window];

 SELECT @init = @init - 1
END

 

Friday, April 28, 2017 - 6:40:58 AM - Pedro Rocha Back To Top (55271)

Hi Brady!

Very nice POST!

It's not rocket science, but it helped me to quickly and efectyvely see how it works, as i am sure it was writen to do!

Regards,

PEDRO

Tuesday, June 7, 2016 - 8:25:42 AM - Jamie Back To Top (41626)

 

USE [master]
GO
ALTER DATABASE [SSISDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO

 

USE SSISDB
GO
update SSISDB.[catalog].[catalog_properties] set property_value=7 where property_name='Retention_window' 
--select * from [catalog].catalog_properties
EXEC  [internal].[cleanup_server_retention_window]  --Cleanup before shrink so nothing important is lost.

USE [SSISDB]
GO
DBCC SHRINKFILE (N'log' , 4000)--leave about 4 gig so all history is not lost
GO

USE [master]
GO
ALTER DATABASE [SSISDB] SET RECOVERY FULL
GO

Friday, January 9, 2015 - 9:37:44 AM - Michael Back To Top (35882)

***UPDATE***

After finally reaching the 90 day history mark, I was able to shrink the SSISDB down from 43 GB to 25 GB and it still has about 3 GB of free space. The size of my operations_messages table went from over 45 million rows to about 26 million (there are multiple jobs that run multiple times per day). 

Be very careful to run the SSIS Maintenance Cleanup job only when there are no other SSIS jobs running with SQL 2012. If the cleanup is running, it will block other jobs from writing to the SSISDB tables and they will timeout and fail.

Wednesday, December 17, 2014 - 10:12:31 AM - Michael Back To Top (35639)

Great post, but your screenshots show that your SSISDB actually got bigger, from 30001.81 MB to 30654.50 MB, after you changed your retention from 365 to 150 days and ran the SSIS Maintenance Job. Did you post the wrong screenshots or are these accurate? I see that the unallocated space also increased.

I have a SSISDB on a client server that is nearly 43 GB. I have so far reduced the history retention (a little each day) from 365 to 230 days (goal is 90 days, not there yet). However, there is little change in the overall size of the database, the number of rows in the operation_messages table, or the free space in the data file. Any suggestions?

Monday, October 20, 2014 - 3:26:16 PM - Michael Back To Top (35021)

Nice explanation! I was curious if anyone has some comments/suggestions on the sizing of the database (catalogs). Ours is approaching 50GB and so far we have not had performance issues. Does anyone have larger?

Wednesday, September 17, 2014 - 8:30:44 AM - Donald.L Back To Top (34563)

*** NOTE *** -

I also found my *.message tables had grown and the Indexes had reduced in size. I'm dropping the retension by 10 per day. Usual time 5 minutes, but now 1.46hr to clean up the 10 days, so be cautious of the amount you reduce.

Wednesday, September 17, 2014 - 8:27:03 AM - Donald.L Back To Top (34562)

*** NOTE ***

Great post pictures made a great help

Wednesday, August 27, 2014 - 11:34:44 AM - Steven Back To Top (34285)

In my current gig, as with a few of the larger enterprise environments I've been in, SQL Agent is disabled by the "DBA Team" (I've never gotten a good answer as to why).

So we have to use other scheduling mechanisms like Tivoli Workload Scheduler to automate sql jobs and maintenance plans so this sort of tip definitely helps.

Thanks for the well written article and helpful tip!

Friday, August 22, 2014 - 5:37:23 PM - Thomson Back To Top (34238)

Sorry Brady for responding too late.

The largest table in SSISDB is internal.executables which is taking only 17mb of space with some 42k records only. Finally, I shrunk the DB and it solved the problem. 

Thank you.

 

Tuesday, August 19, 2014 - 2:42:52 PM - Brady Back To Top (34199)

Koen & Bill,

Thanks for your input for this tip!

Thomson,

If your DB was 40GB and you changed the retention period to 2 and the job only took a few seconds, my guess is that something else is holding space. What is the largest table and how large is it?

Tuesday, August 19, 2014 - 1:43:33 PM - Thomson Back To Top (34196)

My SSIS DB is around 40gb and I changed the retention period to 2 and ran the job, job completed in few seconds and my unallocated space remained same. I wanted to shrink the database to hold only 2days of history. Can someone suggest a way to shrink the SSIS DB.

Tuesday, August 19, 2014 - 1:09:50 PM - Bill Back To Top (34195)

There are indexes added to the tables in SQL 2012 SP1 CU4 and SQL 2012 CU7 to speed up the cleanup job.  http://support.microsoft.com/kb/2829948.  Also, be warned, by default this database is created on the C drive (probably uses the path of master) via a restore command.  We don't allow databases on the C drive except the 4 system databases so this filled up our C drives.  Wish Microsoft would fix that issue.

Tuesday, August 19, 2014 - 8:22:23 AM - Koen Verbeeck Back To Top (34189)

I believe the issue with those clean-up stored procedures is that they rely on "cascade delete" of foreign keys, making them very slow.














get free sql tips
agree to terms