SSIS Database with lots of space
-
Dear all,
I have a SSIS DB in my SQL Server. This database currently uses more than 10 GB of data and I do not understand why. ^$
I think that we are not keeping a lot of history in SSIS and have the minimum logging level. For example, we use simple recovery model but we have the transaction log with 8 GB (twice the size of the data file).
On the other end, I was checking and I found something named query store which I don't understand very well. It has an option to purge query data. What is this data refering too?
Thank you for the support
-
What have you set the "Operations log Retention Period" to? Also what about the "Maximum Number of Version per Project" and "Periodically Remove Old Versions" Settings?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you for your reply. Where can I check those?
- river1 - Tuesday, November 28, 2017 5:36 AM
Integration Services Catalogs -> SSISDB -> (Right Click) Properties
Edit: At the same time, you might as well post what the values of "Current size of Operation Log (KB)" and "Current Size of Version Log (KB)".
Edit 2: I also, initially, forgot to ask for the value of "Clean Logs Periodically".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hello,
CleanUp log periodically = true
Current size of operations log (k) = 311264
Current Size of versions log (K) = 2664Thank you
-
Where can I see this ones:
What have you set the "Operations log Retention Period" to? Also what about the "Maximum Number of Version per Project" and "Periodically Remove Old Versions" Settings?
thank you
- river1 - Tuesday, November 28, 2017 5:55 AM
Exactly the same place...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
-
I'm pretty surprised that your SSISDB is 10Gb in size then. If we have an look at my SSISDB database, it is 4.5GB~ in size, of which 900MB~ is available space (so 3.6GB~). This very much falls inline with what is above in my image, as my operations Log is 3.5GB~ in size, while the versions are a tiny 7MB~. This basically still leaves 100MB for the remainder of the objects for running SSIS. I also have a couple of extra user created objects in there for our website to interface with SSIS.
On yours, however, your Logs are only 300MB~ in size, and your Versions log 2.5MB~. This basically means that there is another 9.7GB~ of stuff in your database. I can easily see that your SSIS is no where near as busy as mine, as my logs are 10 times the size of yours, for half the retention period (I have 21419 logs, you have 339).
What's the space available of your database? I'm wondering if, historically, you didn't have a retention period and possibly years of data stored in there. When someone finally set it, the records were deleted, however, that just shifts the used space into the available space (it doesn't free it up to the server, and I am not suggesting you should).
Are you also putting any User Objects in your SSISDB database?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you very much for your support. Yes. I am surprised too....
Here are some of my database properties, including the ones related with space:
If you look at the log, its very big but we use simple recovery model
-
ALso,
-
It's your log file that is large. That screen shot says that it has a initial size of 8.7GB~. Why is it so large when you have a Simple Recovery Model Database?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, November 28, 2017 7:41 AM
🙂 Don't know also. Maybe it was first in full recovery model and then changed to simple? do we have a way to check this?
- river1 - Tuesday, November 28, 2017 7:51 AM
Nope, unless you have some kind of custom auditing on your server, but then you'd know that already. Who ever made the changes should "know" why they did it and what they did.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Understood. What about maintenace tasks? What are you using for this database?
I am using OLA jobs for all the user databases but not this one.
do you do maintenance tasks in this database? (CheckDBcc and Index defrag)?
What are the best practices for maintaining a SSISDB? Any idea?
Thanks,
Pedro
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply