Is there an advantage to allocating much more space than needed in Sql 2005?

Question

Folks,

I'm migrating 2 databases from a cluttered hard drive to a new one I purchased specifically to host these two databases. The databases are roughly equal in size and projected to grow equally. Does it make sense to allocate half the hard drive to each (so now the databases might have 40% unused space instead of the usual 5-10% I keep it at)? Will this prevent fragmentation since the MDF file will be on a continuous block? Or is this a bad idea? Should I just keep my usual buffer and let the file grow in time (OS can manage defrag). thoughts?

score 1 · Accepted Answer · answered Nov 22 '10 at 22:32

Provisioning as much space as is available is preferable to autogrow, due to the IO load that growing the data file causes, and its unpredictable nature. What if the data file auto-grows by x% in the middle of a busy day? Pre-provisioning space will also minimise fragmentation.

It can be hard to know if you're going to put more databases onto a server, and it can be hard to know what the growth rate of the actual data is up front of a provisioning exercise. Leave yourself some free room on the drive, but definitely keep that buffer space as large as you can reasonably manage whilst leaving some agility for future developments.

I wrote a blog once upon a time on : monitoring key SQL Server stats using DMVs

The code I've provided in that blog could be used or whittled down to help you keep an eye on the size of data vs file size of your DBs.

score 0 · Answer 2 · answered Nov 22 '10 at 21:03

0

I'd say it's a bad idea to just set them 50/50 not, your growth assumptions could be proven wrong and the fragmentation won't be too bad - you can always fix that later if needed anyway.

answered Nov 22 '10 at 21:03

Chopper3

100,240
9
106
238

How much initial buffer is recommended then? – Suraj Nov 22 '10 at 21:09
Depends what you expect as groth. I have a db here that started as 800gb empty and the next expansion will take it over 2tb. In ONE operation. – TomTom Nov 22 '10 at 21:43
I've leave 10% myself – Chopper3 Nov 22 '10 at 22:28
I make these decisions based on the growth trends of my database. You don't want to have to grow often, but it's a waste of disk space to grow a file huge for no reason. Find a balance. – Dave Holland Nov 22 '10 at 23:08

score 0 · Answer 3 · answered Nov 22 '10 at 21:12

The thing to remember is that the database growing the file can be somewhat intensive. I would let SQL autogrow for you, just set the growth to a reasonable percentage based on your growth patterns. As far as file fragmentation I would recommend Diskeeper to you: http://www.diskeeper.com/business/diskeeper/ to help keep that under control. Diskeeper uses what they call InvisiTasking which is basically shadow volume copying, and is able to defrag your live SQL database files (Exchange as well). Put both the DB's on the same drive - let them grow as they will and put a good defragger like Diskeeper in place and you won't have to worry about it.

So the OP accepted this answer then someone voted it down without comment? Can the person who did that say why? — Dave Holland, Nov 23 '10 at 01:40

Is there an advantage to allocating much more space than needed in Sql 2005?

3 Answers3