Over-allocating space for a database, impact on performance

SQL Server 2005:
Assume I have 4 GB of data in a single database. I expect to have 15 GB
after two years. I have a 300 GB logical drive. Is there any harm in
setting the default size of the database to 20 GB? This avoids
fragmentation as new space is allocated to the drive, but creates a "big
shell". Is there a performance hit by sizing it big initially? Data will
be on RAID 5, Logs on RAID 1, and OS on RAID 1.
Thanks,
Mark
Other than the cost of creating the initial file, or restoring a backup,
(both of which are negligible if you have instant file initialization on), I
can't think of anything. (Backups will only see pages with actual data on
them, so it won't affect those.) It's good to size the file appropriately,
if you have the space... ideally, you will NEVER have an unplanned autogrow
event. Things can always change, e.g. after six months you might revise
your two-year estimate and want to grow the file again at some point. But
it is much better to plan this growth for a planned maintenance window or
low volume period, as opposed to letting the file grow in the middle of a
busy day... users will be unhappy.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:OJAsB%239mIHA.1368@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data
> will be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>
|||It sounds like you are taking a set-it-up -and-forget-it approach to space
management. I'd argue that you should always monitor your database space
usage. Leave enough free space so that you don't run into space shortage
unexpectedly. But just don't let over-allocation give you a false sense of
security.
One potential downside of over-allocation is in case you need to deattach,
copy, and attach the database, you'd need to move a lot more data than
otherwise you would.
Linchi
"Mark" wrote:

> SQL Server 2005:
> Assume I have 4 GB of data in a single database. I expect to have 15 GB
> after two years. I have a 300 GB logical drive. Is there any harm in
> setting the default size of the database to 20 GB? This avoids
> fragmentation as new space is allocated to the drive, but creates a "big
> shell". Is there a performance hit by sizing it big initially? Data will
> be on RAID 5, Logs on RAID 1, and OS on RAID 1.
> Thanks,
> Mark
>
>