How do additional files fill?

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?

more ▼

asked Nov 19, 2009 at 12:26 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 19, 2009 at 02:04 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

What about existing objects? Will the data for my table now split across files as I add more data?
Nov 19, 2009 at 02:34 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 19, 2009 at 12:26 PM

Seen: 1546 times

Last Updated: Nov 19, 2009 at 12:26 PM