I want to know what happens when an autogrow event is triggered... Does it hold up all the database processing including reads and writes during that time?

For instance, our database encounters for up to 10 autogrowth avg per day.

Also, what are the cons for not using autogrow and rather pre sizing the database to use the maximum disk space?


Yes, autogrow is expensive, and yes, many transactions will need to wait for it to complete. Search this site and you will see many experts advising to avoid it and many ways to minimize the impact.

The only cons for not using autogrow and instead pre-sizing the file to fill the disk:

  • you can't lease out that space in the meantime (if you know the database will grow, why are you waiting to fill the space?)
  • if you have to grow again, you'll be stuck (but you'd be the same stuck as if you grew incrementally, you just wouldn't get any warning)

I would suggest pre-sizing your files in such a way that there is room for one or two autogrow events before they fill the disk (or exceed quota or what have you). This way you can set up alerting on those events and potentially react in time to prevent downtime. Users shouldn't be waiting for autogrow events unless you are responding to an emergency (and you should obviously avoid those).

| improve this answer | |
  • Hi , Are there chances of shrinking the db partition if autogrow is turned off ? Also, Are there chances of data fragmentation and increase in seek times if we fully extend size instead of autogrow ? If the above are recommended. – Sayantani Nath Jun 12 '18 at 13:02
  • @SayantaniNath Sure, you can shrink it (it is completely unrelated to autogrow setting) but I wouldn't. Why do you need to shrink it? Are you never going to add any more data to this file? If you are, then what is the point? This will potentially cause fragmentation and it will just grow again if you add more data. No, seek times are not increased because the file is large, the file is just a container. – Aaron Bertrand Jun 12 '18 at 13:06
  • Could you share the links where many experts advise to avoid auto grow? – Sayantani Nath Jun 12 '18 at 14:06
  • @SayantaniNath A very quick search turned up this, this, this, and even your own question from May. Make sure to read all comments and answers. But let me turn it around, do you see any experts suggesting autogrowing a lot is a good idea? – Aaron Bertrand Jun 12 '18 at 14:13
  • Not yet But currently my organisation is depending on autogrowth and i need solid valid points to show case why autogrowth may not be the best of the options to rely for a high transactional heavy loaded DB. – Sayantani Nath Jun 12 '18 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.