I have several SSIS packages jobs running, and some months ago my disk got full because the size of the SSISDB database.
I noticed that the cleanup_server_retention_window was set to 365 days, and I changed it to one day. (It is a development server, and at this point I really dont care about the history).
The (big) problem, obviously, is now that the transaction log grows a lot and fast.
To prevent this, I start performing a full backup every week and a transaction log backup every day, and the size of the database is now controlled.
However, some more experienced guys are telling me that this is not the best aproach to this issue, but I cant see any problem with it..
I would like to know if there is a better solution for this.
I tried just about everything including changing retention window; it was deleted the transactions but not reducing the log size. For me the allocated log file size grew to 75 GB. Nothing seemed to help.
The main issue has to do with the recovery model of the SSIS DB that was set to 'Full'. Once I set this to 'Simple' and changed the initial log file size, all was fixed!
I have been monitoring this for the last couple of days just to make sure all is well and it looks fine to me so this operation is safe.
The current log file size is 512KBMB as opposed to 75GB!