0

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?

Sayantani Nath
  • 103
  • 2
  • 11

1 Answers1

3

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).

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • 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
  • @SayantaniNath Autogrowth in general is not bad, it is often a necessary way to balance just taking all the disk and not causing performance problems by growing all the time. I will tell you that constantly shrinking your files and then letting them autogrow all day long is absolutely, 100% terrible. You can believe me or you can go do your own research, let me know if you see anyone suggesting otherwise. Do you need evidence from someone smarter than Paul Randal? – Aaron Bertrand Jun 12 '18 at 14:26
  • Hi Aaron, I am not able to decide should we keep the autogrow for our current prod database or remove it and presize our database by allocating sufficient storage. Currently it is set to 200 MB and daily there are around 10-12 autogrowth , max to 2.4 gn data. Can you suggest anything ? – Sayantani Nath Jun 14 '18 at 07:20