I am trying to optimize some settings on a SQL Server that talks to inhouse programs. I have read to size all DB files the same. I have a server with a 20GB data file, with the other 9 files being significantly smaller. I was going to resize the 20GB data file to be 40GB to allow for growth. Should I change the other 9 files to be 40GB as well?


It sounds like you are confusing having multiple data files that make up a single database and having multiple databases each of which have one or more data file(s). Data files (.mdf for primary files or .ndf for secondary files usually) for different databases should be the appropriate size for the database and have no relation to each other. If you have multiple files within a single filegroup within a database then yes making them the same size might will have some advantages. But to be honest I'm not certain.

The only time I've heard it specifically mentioned that data files should be the same size is specifically for the tempdb database. In that particular case there can be some significant advantages to having data multiple files (for tempdb) but only if all of the multiple data files are the same size. This may be what you were reading.

EDIT: I'm adding some additional information based on the comments below and an effort to give a more complete answer.

A database is made up of 2 or more files. The primary two files are the data file (usually .mdf) and the log file (usually .ldf). Additional data and log files can be added. Additional log files (still usually .ldf) are usually added to extend the log onto a different drive with more space. Since they are only written to sequentially there is no issue with them being of different sizes. That brings us to data files. The additional data files (usually .ndf) are grouped by filegroups. A database has a PRIMARY filegroup and can have additional filegroups. It is recommended that the data files within a filegroup be the same size since SQL Server uses a proportional fill algorithm to put data into them. In other words if you have two files in a filegroup and one of them is 1GB and the other is 2GB any data written to the filegroup is twice as likely to be put into the 2GB file.

To sum up. If you choose to have multiple log files (and a lot of people recommend against it) then it doesn't matter if they are different sizes. The only time data files should be the same size is if they are within the same filegroup on the same database.

Bonus information: Any time I said "Same size" that means not only the same physical size but also that the growth settings should be identical. For what are hopefully obvious reasons.

  • I found the data file sizing comment here: technet.microsoft.com/en-us/library/cc966534.aspx – user33486 Jan 28 '14 at 18:00
  • 1
    I assume you mean #9 on the list? It could probably be more clear but it is talking about multiple data files within the same filegroup. Filegroups are parts of a single database. I could be wrong but from your question it sounds like you are talking about multiple data files for multiple databases on a single SQL Server instance. – Kenneth Fisher Jan 28 '14 at 18:18
  • I understand what you are saying now. It sounded wrong to me and didn't make much sense to me. Thanks for your help. – user33486 Jan 28 '14 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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