Janie.Carlisle (1/5/2011)
hmmm I actually like the idea of turning off autogrowth and forcing the new data to the new filegroup. I think I will do that. It doesn't solve the issue of the space being full but it ensures no new data will go there. Thanks!
new file not filegroup. you risk running out of space within your current filegroup that way. You have to add a new file to your current filegroup.
Unless the existing file is almost totally full you cannot completely force all new writes to the new file. If the new file is emptier than the old file more data will be written to the new file, but that is about all you can guarantee. to maximise that effect try and make the new file at least the same size as the old file.
for filegroups with multiple files SQL writes to them in a round robin fashion. So if there are 4 files in a filegroup SQL will write to File A, File B, file C then file D, then back to file A. If one of the files is emptier, proportionally more data will be written to that file. SQL attempts to fix it so that the files become full (and therefore need to grow) at about the same time.
---------------------------------------------------------------------