If I add a new file to my single file filegroup, will objects get moved to the new file? If I add data to a table that is only in my current file, is the new data added to both files?
asked Nov 19, 2009 at 12:26 PM in Default
Steve Jones - Editor ♦♦
Within a filegroup, SQL Server will normally fill up the files in proportion to the size of the respective files. In other words, if you have multiple files in a file group and an object that will occupy more than one data page, it is highly likely it will have portions in more than one file.
Of course, that only applies to files within one filegroup. SQL Server will not split the data for a single object accross filegroups without being explicitly directed to through something like partitioning or else having text/ntext/etc columns assigned separately. Objects will not be moved between file groups unless it is explicitly done by a user with appropriate permissions.
Edited to answer Steve's comment: (too many characters to answer in a comment)
I have not been able to find an authoritative reference to what happens with existing objects, but brief testing seems to indicate they will start splitting newly added data between the files in a way similar to when all files existed before data was added.
This of course still applies to newly added data. Data that was there previously will not be redistributed when a new file is added.