August 22, 2018 at 12:09 pm
I am confused with this two terms. Can anyone explain what is the difference between database growth and shrink?
Thanks for your time.
August 22, 2018 at 1:20 pm
elea.grig - Wednesday, August 22, 2018 12:09 PMI am confused with this two terms. Can anyone explain what is the difference between database growth and shrink?
Thanks for your time.
Database growth is exactly what it says. When it's enabled (which it is by default, although at a HORRIBLE value) if the database file needs to get bigger to accommodate incoming data, it will "grow" the file.
Database shrink is the same, only in reverse. If the database has free space (say from records being deleted,) then (if autoshrink is enabled, and it's generally a very bad idea) SQL will "shrink" the file down as much as possible. As for why this is a bad idea, it causes all sorts of problems: The application has to wait while the DB shrinks, which if a lot of data is removed can take a long time, indexes get fragmented badly, the autoshrink is stupid and won't allow for the possibility of new data being inserted in place of the deleted, so now the file shrinks, only to have to grow again immediately afterwards, and I'm sure there's other problems I've missed.
Auto-growth, however, isn't a "bad" thing, if the growth is set to a sane value. Ideally, you would still pre-size the DB so it shouldn't need to grow, then monitor it and when it might need more space, add the space manually during a maintenance period, as growing the file is a very IO intensive operation that will impact performance badly.
August 22, 2018 at 6:37 pm
Jason has a good explanation, but a couple other points.
1. Enable IFI for your server to let data files grow quickly
2. Set some monitoring up to look at free space in your data file. When it's low, manually add space as Jason suggested. I try to keep 3-4 months pad of free space in my database. If I get below that, I grow again. I do this on a monthly basis.
3. Just don't use Autoshrink. While Jason has given you reasons,just don't do it. If you need to reclaim space, this should be a one-off event. Do this manually.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply