Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

You're Invited: Easily Visualize Your SQL Server Result Sets with Charts and Graphs
 

Managing the size of the SQL Server SSIS catalog database


By:   |   Read Comments (15)   |   Related Tips: 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.


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


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

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

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 07, 2016 - 8:25:42 AM - Jamie Back To Top

 

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 09, 2015 - 9:37:44 AM - Michael Back To Top

***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

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

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

*** 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

*** NOTE ***

Great post pictures made a great help


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

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

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

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

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

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

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


Learn more about SQL Server tools