We have a primary file group with several files each on different disks. One of the disks is nearing capacity and as such we have had to switch off the auto growth for that file. Is there anything I need to consider when I do this?

I don't believe I can force a table to be in a particular file when they are all in the same filegroup, so from an admin point of view I haven't been able to think of anything.


I don't believe I can force a table to be in a particular file when they are all in the same filegroup

That's correct, you do not have the direct granular control over file allocations, only filegroup.

With that in mind, you have a few options. The first one would be to create a new filegroup and isolate whatever tables you need to in order to give you your desired end result of not filling up the drive. Another one, which I'd argue is probably one of the better options, is to put the file in question in a location with more space, or tack on more space to the existing volume. I'm guessing you wouldn't be asking this question if that was a possibility, though.

Another route would be to take advantage of the proportional fill behavior that SQL Server implements (which dictates it'll attempt to fill up all filegroup files at the same time) and size out (larger) the other files in the filegroup. That should translate to a larger percent of the allocations in those other files, as they'll demand more by the ratio of free space (reference this BOL document to further understand proportional fill). Although I personally think that's a hack and a workaround at best, and will only prolong the inevitable of filling up your drive.

  • Correct we can't expand the volume, so adding a larger volume and moving the file to it would be my only other option. – Tom Jun 19 '14 at 11:51
  • And that's not a bad or uncommon option at all. – Thomas Stringer Jun 19 '14 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.