Hi,
I have a quick question
If I have a database with a primary filegroup and one ldf and one mdf residing on it. We created a second filegroup and then in the database we created a new ndf file on that second filegroup.
Question - Will SQL server write data to both the primary and secondary mdf and ndf files while they are in different filegroups using the round robin method?
I understand that SQL server will fill the files in a single filegroup, but will it do the same when the ndf files are in different filegroups?
Any help is appreciated.
Jeff
No, if you create a new file group you must put objects on the file group for it to be used.
EG
CREATE TABLE MyNewTable(
….
) ON [MyNewFileGroup]
The proportional fill algorithm requires the files to be in the same file group not separate and will target the file with the most free space in that file group more favourably than the others.
Say you had a 1TB mdf, and that is 900GB used, you create a 1TB ndf and put that in the PRIMARY file group, you would think SQL would split the writes 50/50 as they in the same file group and same size, but no the writes would be done in approx 10/90 in favour of the NDF as that has the greater free space so the data is not evenly distributed.
Which is why it is imperative to try to get file placement right at the beginning, or if you want to do it afterwards, move the contents from one file group to another.
January 20, 2022 at 3:05 pm
Thank you very much - that is what I thought. I marked your reply as "Answer"
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply