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
  • 181,950
  • 28
  • 405
  • 624