Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
How much initial buffer is recommended then? –  SFun28 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
add comment

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.

share|improve this answer
1  
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 1:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.