Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TJTodd
Starting Member
10 Posts |
 Posted - 2010-08-30 : 10:29:35
|
| We have an environment with a single data file and want to add 2 additional data files (on different LUNs). Usually when we do something like this we just let those files "naturally" grow with newly-added data.If we wanted to try to spread existing data onto these new data files, would INDEX REBUILDS be the best bet for doing this? I'm assuming that while the index rebuild is executing that it will utilize the free space on these new files.Any help would be appreciated. Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-30 : 11:46:51
|
| you can create a clustered index on the new filegroup to move the table.some will choose to keep table on one, non-clustered index on the other, in which case you can drop and re-create your non-clustered indexes on the new filegroup |
 |
|
|
TJTodd
Starting Member
10 Posts |
Posted - 2010-08-30 : 11:52:19
|
| Thanks for the response - should have mentioned that there's only 1 filegroup in play. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-30 : 11:57:11
|
| you can't specify which file to place the data in, only the filegroup. so when addign files, you should create a new filegrouplink below may helphttp://msdn.microsoft.com/en-us/library/ms179316(v=SQL.100).aspx |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-30 : 12:00:38
|
| see this article too: http://www.sql-server-performance.com/tips/filegroups_p1.aspx |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-08-30 : 13:55:34
|
| Index rebuilds will move some of the data to the new files in the same filegroup. Over time, index rebuilds should spread the data out fairly evenly over all the files. The table data must have a clustered index for it to move.Note that index defrag will not move data between files.CODO ERGO SUM |
|
|
|
TJTodd
Starting Member
10 Posts |
Posted - 2010-08-30 : 14:40:29
|
| Thanks.After adding the new file I was able to confirm the moving of data after running a DBCC SHOWFILESTATS before and after the Index Rebuild.Thanks! |
|
|
|
|
|
|
Best way to spread data to newly-added files?