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.