April 23, 2024 at 6:05 pm
April 23, 2024 at 6:26 pm
Does your data (and business) have seasonal or annual surges of inserts? Is it relatively stable year-to-year
If not, then you can make somewhat useful projections, though they'd probably be more predictive w/ at least of year of history.
If you want to be conservative, pick your largest growth month, and allow for 5GB/month growth.
If you want to be very conservative, use your largest growth percentage, & allow for 50% growth per month.
If you want to use averages, use your average growth/month, throwing out the artificial shrinking due to truncation, unless that is a normal event.
April 23, 2024 at 6:55 pm
Is there are db growth rate formula which can be used to predict say next 3/6 months of db growth rate.
Yes. It's known as "Linear Regression Analysis". I'm giving a presentation on that the evening of 24 April 2024 including how to use it to make such (when will it run out of room or how big will it need to be to last until) predictions of all of your disks. Please see the following Meetup meeting notice.
https://www.meetup.com/glass-greater-lansing-area-for-sql-server/events/300421060/
Advisory... bring your water-cooled helmet. This is for data analysts, data scientists, and DBAs and is suitable for beginners that want to rapidly become "expert" in this particular, very important area.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2024 at 7:35 pm
I agree with ratbak here. But I would also like to point out that with so few data points, the estimates are going to be a bit off. But eyeballing that, it looks like you grow approximately 6 GB every 3 months. Jan-March = 6Gb. March to June (removing the outlier as you did a shrink) = 6 GB. In order to get meaningful estimates, you want a lot of data points over a long period of time.
But is that "normal" for your business? I have some DB's that get hit heavy some months and have no growth others. My preference is to pre-grow, not auto grow, wherever possible. So if I expect the database to be 50 GB (approximately) by the end of the year, I will pre-grow it to 50 GB in January and then check it in July to see if my estimates were good. If it looks like it is growing at the rate I expect, I review again in November. If July data looks off, I'll adjust my estimates and plan for a pre-grow operation some weekend/evening to minimize the impact. Our IT department budgets disk every fiscal year, so I estimate the growth based on the previous 3-5 years of data and if I know of any upcoming pattern changes. I then pre-grow the databases to utilize the disk.
The other thing we are missing from you is your autogrow settings. If you are set to auto-grow by 5 GB (for example, doesn't look like that is the case, but MAYBE), then the growth for the next 3 months would be 10 GB by my estimate. Why 10 GB? because you have growth of 6 GB every 3 months (approximately) and autogrow is 5 GB.
IF the database is "new", then semi-accurate growth is going to be hard to calculate and you'll just need to guess based on business use and your gut, plus some wiggle room. I have monitoring tools in place (RedGate SQL Monitor) and it shows me the growth over time and has estimated dates where autogrow will trigger. It's all estimates based on how far back I have the data going.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply