With multiple files in a file group, when does data start being placed on the 2nd file in the filegroup?

Question

In a previous question, it was suggested that I should add an extra "File" to my existing (Primary) Filegroup to easily add more disk space for my DB.

So I did that and nothing broke.

But I'm not sure when Sql Server will decide to stop putting data into the original File and start putting data over to the new File?

From @davidbrowne's excellent answer to my question, he mentioned:

SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files.

Okay, great - but when does it 'know' to start filling the other file?

Currently, this is what I have setup ... and please note the MAX FILE SIZE.

Do I need to give a MAX file size (e.g. LIMIT to 1T) for the original File which will then help Sql Server to 'know' when to start using the next (new) File?

There's currently 19GB left on disk F:\.

Answer 1

You'll want to preallocate the space on XWing_ExtraData. At 10MB per autogrow, it'll induce a lot of external file fragmentation and delays whenever SQL needs to expand the file on the disk (performance will be better if you have a large pre-sized file up front).

Then disable autogrowth on XWing so that it can't grow any further otherwise SQL will continue to populate the file until you run out of space, triggering an 1105 error (filegroup full).

With regards to the original file, my preference is to just disable autogrowth via the dialog box (checkbox on the top of the dialog box or via ALTER DATABASE...MODIFY FILE...FILEGROWTH=0. You want to ensure that SQL won't attempt to autogrow the file if the algorithm can choose that file instead of XWing_ExtraData if both need to autogrow to accomodate the next data insertion.

Answer 2

Proportional fill is based on the anount of free space in each file.

For example, if you have 2 files in a filegroup, file1 and file2, where free space in file1 is 1GB and free space in file2 is 2GB, 66% of writes to the filegroup will be written to file2 and 33% will be written to file1.

In order for proportional fill to work, you should presize all files in the filegroup to the same size, so the amount of free space in each file reflects the proportion of writes you want. Is the XWing file full?

If so, you probably want to resize XWing_ExtraData to be big enough to hold slightly above 50% of the space allocated in XWing, then shrink the XWing file to the same size, using DBCC SHRINKFILE. That will move data from the original file to the new file. You also may want to defragment indexes stored on the filegroup afterwards since shrink tends to fragment data.

See Microsoft Docs for details about DBCC SHRINKFILE.

As MattyzDBA said in their answer, you should set maximum file sizes for data files. You should also set a sane autogrowth amount, probably in the neighborhood of 1GB, assuming you've given the SQL Server service account the "Perform Volume Maintenance Tasks" permission through the Local Security Policy applet. See this for details about that setting. Presizing files and setting sane autogrowth settings are widely considered to be "best practices".