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.

Currently I have a SQL Server database which is about 2 Gb. I know over the next year it's going to grow to a maximum of about 10Gb. Hard drive space is not an issue in the slightest.

Is there a down side to resizing the datafile to 20Gb now, then defragmenting the hard drive?

Should I resize the log file to 1Gb as well? Something ridiculously large so that fragmentation doesn't happen there either.

With this question I would like to avoid the datafile becoming fragmented on the disk itself, but I don't want to negatively impact performance.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

If you can predict your MDF and LDF sizes, go for it.

As well as partition alignment and format with 64k cluster sizes (as per TomTom's answer), also remember that to defrag a table (index maintenance) requires free space in the MDF of around 1.2 times the size of that table. So you need around 7.5GB free on your MDF for a 6GB table. This is a common cause of unexpected MDF and LDF growth.

share|improve this answer
add comment

Fragmentation exists at 2 levels: SQL and OS. OS fragmentation will happen only if the file pre-sized has been done on a fragmented drive. If you size the file according to your maximum needs and disable auto growth the disk will not get fragmented unless you manually move some data to a new data file created.

You should anyhow check SQL fragmnentation using dbcc showcontig.

So yes, for your question: best practice is to give a size according to growing needs. In this way, SQL will not loose time in space allocation and also, keep SQL data bytes in a contiguous order order on disk.

share|improve this answer
add comment

I always resize db's to a senseible size.

More important, though, is proper partitioning, especially when you use RAID units. Make sure your partitions align as well. And custom NTFS formatting with 64gb node size.

share|improve this answer
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.