June 9, 2008 at 11:19 am
I've had auto-shrink turned on on our main production database for a while.
In the mean time, I've read that this wasn't such a good idea, because of data fragmentation, among other reasons.
I have two questions, then:
1) Can I/should I turn auto-shrink off and what would be the consequences of doing so?
2) What can I do to reverse the data fragmentation?
June 9, 2008 at 12:20 pm
The main usual consequence of turning off auto-shrink is that your whole database speeds up.
Secondarily to that, your database might grow a little bit now and again.
The way to handle the fragmentation is to (a) defrag the hard drive, (b) have SQL Server defrag tables and indexes in the database (you can build a maintenance plan that will do that for you - it's a good idea to have something like that running on a schedule anyway).
To handle the growth, check the database periodically and work out how fast it's growing. Estimate how much it will grow over the next six months or so, and add that much to the file size. (If you schedule regular disk defrags, a little addition to the database file size now and again is no big deal.) Then, set the database file to automatically grow by about that increment when it needs it. Don't set it to a percentage, set it to a relately large fixed amount.
For example, if you track your database for a month, and find that it grows by 10 Meg during that time, then you can estimate that the database will grow by something like 60 Meg in 6 months. Add 100 Meg to it now (to account for increased activity over time), and set it to grow in 100 Meg increments. Review it yearly or so and see how it's doing on that. Should be fine.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 12:30 pm
Yes, it does.
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply