Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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,

share|improve this question

2 Answers 2

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.

share|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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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