I've got one MDF file that I just copied over during an upgrade from SQL Server 2005; and I've added 12 more NDF files to that FILEGROUP now that I've added.
Ideally, I'd use DBCC SHRINKFILE (File1, EMPTYFILE) to rebalance; but it's going surprisingly slow. I'd like to move the 70Gb around in more manageable chunks; but doing a DBCC SHRINKFILE without EMPTYFILE doesn't actually push data into the other files; and EMPTYFILE, well, goes until it empties the file.
Any suggestions on how to do this more gradually?
Thanks,
Best Answer
You can rebuild your indexes (defrag won't help any as it only moves data within the same file). If that isn't good enough then create another file group, and rebuild the cluster indexes into that new file group. This will move the table to the new filegroup. Then rebuild them again back into the original filegroup which will move them back. Do the same for the non-clustered indexes.