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

avatar image

Steve Jones - Editor ♦♦
5.2k 79 94 87

(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

avatar image

15.6k 22 55 38

(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.

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: 2151 times

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

Copyright 2018 Redgate Software. Privacy Policy