SSIS solution internal database SSISDB bloated

Question

I am developing ETL using SQL Server x SSIS.

The database SSISDB (SSISDB.mdf) in the SSIS solution is bloated.

It seems that the disk on which the database is placed is being squeezed,

I would like to know how to compress the file size. Is it acceptable to implement compression? I would also like to see the impact on the SSIS ETL application.

Also, what kind of data is in the SSISDB.mdf file?

Answer 1

Answer 2

Here is a quick reference you can check it out:

[https://www.sqlservercentral.com/articles/setup-ssis-catalog-retention-policy-and-partial-cleanup-strategy

The key is too change your retention_window to a smaller value other than the default 365 days.

Check this:

select * from [SSISDB].[catalog].[catalog_properties]
ALTER DATABASE [SSISDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO

declare @index int = 364, @max int = 30
while @index > @max
begin
exec [catalog].configure_catalog RETENTION_WINDOW, @index
EXEC [SSISDB].[internal].[cleanup_server_retention_window]
--shrink the log file 
DBCC SHRINKFILE('log',100)
set @index = @index -1
end


ALTER DATABASE [SSISDB] SET RECOVERY FULL WITH NO_WAIT

Answer 3

Hi @Naito, Kazumi

You inspect objects, settings, and operational data that are stored in the SSISDB catalog, by querying the views in the SSISDB database. You manage the objects by calling stored procedures in the SSISDB database or by using the UI of the SSISDB catalog.

You may refer to Efficient maintenance of SSISDB to maintain SSISDB database.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Thank you for your reply. I will check it.

There is one more puzzling thing.

As follows,

A disk size of 51,635,584 KB is observed on the physical disk.

but,

When checking from the properties of the SSISDB catalog,

As follows,

Version Log is 2,320KB,

Processing log for maintenance is 545,992KB

and divergence can be seen.

What other data is being held and causing disk pressure?