• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 48
  • Last Modified:

Which of this one to be considered for SQL Autogrow file size. (SQL database size growth value or data file(s) size growth value?

I got a recommendation to set the SQL Autogrow file size and that recommendation is based on weekly DB growth size.
For that, should I have to take database size growth or data file(s) size growth?
0
Varshini S
Asked:
Varshini S
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, you shouldn't choose a size like that. The idea should be avoid many auto grows as they can block the database.
Best solution is to provide the necessary size to support at least the expected database grow for the current year. You can take the last year size information to predict the next year grow.
Also monitor it and have an alert fired when only 10% of empty space is available so it gives you time to give more storage if necessary.
0
 
Varshini SAuthor Commented:
Hi Vitor,

Instead of weekly you are recommending to take the yearly value. Right?
 file
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, try with yearly.
Look, you have 16MB now. If that's you average week database grow, then multiply it by 52 (number of weeks in a year). It will give you 832MB.
If you think is too much, then multiply it by 16 (a quarter as 16 weeks). This will give you 256MB, which I think is the minimum acceptable.
There's really no need to have and autogrow of 16MB (it's just too small grows).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now