0

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.

Thanks

Blootac
  • 177
  • 1
  • 2
  • 9

1 Answers1

0

Rebuild your indexes:

exec sp_MSforeachtable 'alter index all on ? rebuild';
Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
  • 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