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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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 (
The only time I've heard it specifically mentioned that data files should be the same size is specifically for the
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.