SSIS Database with lots of space
-
Also, I don't understand why, for the data file we have so big size and just a few used:
Why do you think I have a so big data file?
- river1 - Tuesday, November 28, 2017 9:16 AMAlso, I don't understand why, for the data file we have so big size and just a few used:
Why do you think I have a so big data file?
We maintain the SSISDB as per any other user databases, usual Ola jobs are fine.
Again you will probably have had the default 1 year retention period set and then changed it to 14 days and you have had a huge data purge causing the log to grow and also all the used space to be returned to the data file. So the purge when changed from 365 to 7 will cause the log to grow to make the deletes, and then move the used space into reserved space as a normal delete would do.
-
As Anthony said, I treat SSISDB just like any other user database. it is backed up on the same schedules, by the same scripts etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Understood. Makes sense. thank you.
This is development environment. in PROD, situation is much differente. Let me show you please
-
- river1 - Tuesday, November 28, 2017 9:48 AM
Why treat development different? Dev should be backed up almost (if not as) frequently as Production in my opinion. That's where are the work hours are going into; if you lose that server then you could potentially be losing days of work hours.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
The Data file is almost full , but I have just a few records..... As for the log file yes its big more then 2GB and in simple recovery model.
It seems that the retention period is of 28 days. What is the best practices advicing for this SSISDB in terms of retention period? This is retening what? all the transactions that were done?
Thanks
- river1 - Tuesday, November 28, 2017 9:54 AM
That is up to you to decide, how much history do you need to successfully satisfy the business needs or even your internal needs? Then you set the value to whatever it should be to meet your requirements.
Have you looked through the execution reports in the catalog? You can see it stores a lot of information about what your packages do, start times, end times, messages, errors etc etc all that has to be stored somewhere that is whats it is retaining, not the individual CRUD statement a DFT did but the metadata and event handler information, if your not bothered about any of that history or information simply set retention to 1 day and let it purge daily.
-
Let me just add all the production information. So that I can speak with you with more data.
By the way, thank you very very much for all your support.
Here we go. PROD:
-
-
What's the question based on that latest picture?
If you feel that 14GB is to big for your data file you will need to reduce the retention period, to what value, that is for you to decide based on how long you need to keep such information for business and internal needs. Then you let the purge job purge out and you can reclaim the space.
-
- anthony.green - Wednesday, November 29, 2017 1:26 AM
Yes. Makes sense (as always 🙂
But I would like to understand what is the data that is stored as retention data. Where can I find information about what type of data is stored by the retention functionality?
From the table above, it seems I have 5 or 6 tables very huge so probably this where this data is stored (data kept by the retention functionality).
Any idea of the information that is contained in this tables?
-
It is information based on the executions of your packages, start times, end times, durations, any messages, anything from the event handlers, execution statistics. The more packages you have, the larger they are and more complex the packages are will all add space to the utilization as you get metrics on each and every object inside the packages. So you can see how a package did as a whole or an individual script task or DFT.
As per my previous comment, take a look at the reports inside the SSIS catalog, see if the information it gives you is any use to you, if not don't bother storing it and set your retention to 1 day, if it is see how long you need to keep it for and set the retention to an appropriate value.
The fact it is set at 28 means someone has gone in and changed it already from 365, there must be a reason for that, probably to see how performant your packages are over the past month
-
Indeed. You need to know what is right for you. For example, like I said, mine is set to 7 days. The reason being that we run a LOT of SSIS tasks. If I stored 365 days of logs for SSIS, I wouldn't be surprised if SSISDB was over 150GB~ in size just for the logs. What you need is up to you or whoever makes those decisions in your business; they know better for your environment.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply