Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database in a single file group, with a single file group. I've added 7 data files to this file group. Is there a way to rebalance the data over the 8 data files other than by telling sql server to empty the original? If this is the only way, is it possible to allow sql server to start writing to this file? MSDN says that once its empty its marked so no new data will be written to it.

What I'm aiming for is 8 equally balanced data files.

I'm running SQL Server 2005 standard edition.


share|improve this question
up vote 0 down vote accepted

Rebuild your indexes:

exec sp_MSforeachtable 'alter index all on ? rebuild';
share|improve this answer
Will that distribute the tables across the files as well? or just the indexes? – Blootac Apr 26 '10 at 18:11
It will redistribute data for all indexes, including the clustered index, which is what one usually calls the 'table'. For heaps (tables w/o a clustered index) things are more complex in SQL 2005 (you need to copy the data). In SQL 2008 you can use ALTER TABLE ... REBUILD for heaps. – Remus Rusanu Apr 26 '10 at 20:55

Your Answer


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.