July 26, 2006 at 3:51 pm
SQL 2000
Almost all (50) of our databases are set to autogrow and autoshrink.
These databases are not edited most of the time.
Only when content needs to be changed maybe couple of times every 6 months.
Does SQL Server tries to shrink a database if it hasn't grown?
I analyzed the datafiles drive and got a 97% file fragmentation.
Now i have to defragment this drive sometime in the future.
Should i turn off autogrow and autoshrink and set desired database size?
Thankx
July 27, 2006 at 2:56 am
Both autogrow and autoshrink will give hard drive fragmentation, but autoshrink is the real killer.
If you start with a minimum size database and do maintenance such as index rebuilds, the database size will increase. As soon as the maintanance is over, SQL will start its autoshrink process. It is likely that the space released will not be the same disk fragments as the space grown, so you end up with disk fragmentation. Repeat this over a few months and you end up with very long disk fragment chains and spend too much time moving the disk head instead of reading data.
I cannot think of any situation (except maybe in a PDA, etc) where autoshrink is helpful. You should look at turning it off as soon as possible.
Autogrow is not so bad, providing your growth interval is sensible. If you have a database that is continually growing and have a growth interval of 1 MB you will spend a lot of time growing the database which impacts query performance, and get a lot of disk fragmentation. For small databases a growth increment of 20% is reasonable, but my preference is to set a fixed growth of 10MB for all databases under 50MB, and review the value as the database grows. If you have a database of 10 GB, then a growth interval of 500 MB or 1 GB is reasonable. If you use a sensible growth increment you minimise disk fragmentation.
SQL Server treats database growth as synchronous, so when you grow the database all DML activity within the database stops. For a large database you need to balance the time it takes to grow a large fixed increment against the fixed time overhead of any database growth and the impact on disk fragmentation.
For any database, setting the proper initial size is important. If you know your database will grow from nothing to 12 GB in 6 months, then a further 2 GB per month , set the initial size to 12 GB, with a growth increment of perhaps 2 GB. If your database will stay steady at about 50 MB, set the initial size to (say) 60 MB with 10 MB growth.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply