5

I found numerous questions / answers about SQL-Server auto-growth - almost all indicating that one should try to avoid auto-growth if possible.

However auto-growth is available with MS SQL Server and sometimes required.

Is there any way how one could predict when SQL Server will trigger it's next auto-growth event or is this calculation completely internal to SQL Server?

(E.g. is there some kind of "fill-level" in settings which SQL Server compares to the current database usage and then triggers auto-growth when this fill-level will be reached?)

BR Mark

2
  • @JonSeigel this is more precise now. My question is about auto-growth. (I wasn't even aware there's auto-shrink too. Is it?)
    – vxmark
    Commented May 1, 2014 at 20:39
  • There is. It should be avoided. brentozar.com/archive/2009/08/…
    – ACDBA
    Commented May 1, 2014 at 21:22

2 Answers 2

2

I think the best way is to go based off of previous events, and one way to do that is to use a trace to start capturing history.

Also some ideas here:

As you can see, there are many ways to approach this. Let me know what you think.

2
  • Thank you for these useful links! What I learned from those is that we can either try to predict future auto-growth events from evaluating past auto-growth log data or we can set event notifications for currently happening auto-growth actions. As long as nobody comes up with something better log data evaluation will be my best bet so far. But how to predict the very first auto-growth operation (when there's no log data available)?
    – vxmark
    Commented May 1, 2014 at 21:03
  • That will be tricky. You could compare it to other systems based off their load (if that's possible), but really that's a tough one that maybe someone else could give some advice.
    – ACDBA
    Commented May 1, 2014 at 21:23
1

Know your app.

If your database has 14 GB of free space in the data files and your app is generating 2 GB of activity a day then there will be an auto-growth event in 7 days. If your database is set to "Autogrow by 4 GB" then there will be an auto-growth event every 2 days after that.

Data Collection is an easy-to-set-up monitoring system that allows you too look at the history f database growth for a number of servers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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