0

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,

|improve this question
  • Entertained that I have no upvotes, but more than 1k votes. ;p – Rizwan Kassim Oct 18 '15 at 5:09
1

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.

|improve this answer
0

I believe that you will have better results if you reindex the data. That will cause SQL Server to try to move the data around, as long as your tables have clustered indexes. You can reindex the tables one at a time, manually, or wait until your next reindexing maintenance window. I would make double-plus sure that the other files have enough space; you do not want to rely on autogrowth for this.

|improve this answer
  • Reindexing moves stuff around; getting me closer to my goal, for sure. But it doesn't technically solve the problem... – Rizwan Kassim Nov 30 '10 at 5:01
  • I'm not sure that I understand. Reindexing a table will move data from one file to the other files in an "unbalanced" file group. Reindexing one table at a time provides some control over how much work is done at any given time. Note that SQL uses various file attributes (filegrowth, free space, max size) when deciding where data goes. You should be able to move data with DBCC SHRINKFILE (File1, 70000), and then keep dropping the number. BUT that tends to lead to heavily fragmented indexes and bad performance, so you would want to run a reindex anyway. – darin strait Dec 1 '10 at 14:57

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.