SSISDB cleaning SQL server 2016

Question

I've got a real problem here. I've been checking the internet but can't seem to find the answer. I have a database where the SSISDB is almost 70GB and I can't seem to clean it.

i've executed the internal procedures but they don't work: - cleanup_server_retention_window - cleanup_server_log

I checked the informatie from these posts:

can someone please help me I already set the "Clean logs periodically" to True and the "Retention Period (days)" to 10 but nothing happends and I still don't have space freed up which is becoming a problem right now.

I've found this with help of @panagiotis-kanavos:

if I want to keep 25 days of logs what would be an ideal size?

How do you know the space wasn't freed? Did you check the total size or the free space size? Cleaning up the database won't *shrink* it. — Panagiotis Kanavos, May 08 '18 at 12:10
I'm checking the diskspace of the server (which is still the same) and the properties of the SSISDB which is also still the same — rick van der horst, May 08 '18 at 12:11
The disk space has nothing to do with the free space in the database. Which properties did you check? Did you actually check the data size, free size, log size? — Panagiotis Kanavos, May 08 '18 at 12:12
The transaction log won't be cleared until you back up the database either. — Panagiotis Kanavos, May 08 '18 at 12:13
Overrule what? If you mean the transaction log, don't even think about it. That's like trying to hard-wire a fuse box. The transaction log is *essential* for recovery. Take a backup and then shrink the database to a size suitable for 10 days of use. — Panagiotis Kanavos, May 08 '18 at 12:17
You can check the used/free size per file (data, log) from the `Tasks > Shrink > Files` menu. — Panagiotis Kanavos, May 08 '18 at 12:18
I've heard multiple people say that you should be really carefull when shrinking a database. so is this a good solution? — rick van der horst, May 08 '18 at 12:22
That's why I'm saying you should shrink it to a size that's enough for 10 days. If you shrink it to the minimum the server will have to reallocate that space as the database increases again. This will waste IO and probably result in fragmentation. — Panagiotis Kanavos, May 08 '18 at 12:31
I've uploaded a picture with the question what an ideal size would be in the original post. could you check this for me? as you can see I currently have 10 days chich adds up to about 200MB of logging — rick van der horst, May 08 '18 at 12:32

score 0 · Accepted Answer · answered May 08 '18 at 12:51

0

The information given to me by @Panagiotis Kanavos has been the answer for me. there for the answer is given and I can close this question

the answer concretely was; Shrinking the table size to the correct size for the setup i'm currently running.

answered May 08 '18 at 12:51

rick van der horst

79
1
14

SSISDB cleaning SQL server 2016

1 Answers1