data-konzept

'Periodically we are asked how to split an existing filegroup into multiple data files. The quick answer is that SQL Server does not have a built-in way for you to do that automatically, but you can do it yourself. The process is relatively simple and I have provided a script that demonstrates one technique. The script provided is not designed for production and is only provided for illustrative purposes.

There have been many articles in the past that talk about using the ALTER INDEX …. REBUILD option to move objects from one filegroup to another and to “rebalance” that way. This author acknowledges the benefits of that technique, but sometimes the question of “rebalancing” is more driven out of simple “geometry” constraints. For example, if I have a database on a volume that I cannot grow, and I simply want to add new files to the filegroup – but have those files reside on a different volume. Adding the new files is quite simple, but by default, the existing file remains essentially full and there is an imbalance between the old and new files.

The technique proposed here will effectively rebalance and move the data out of the existing file across to the new files in such a way that the original file can be reduced in size and thus free up space on a volume that is filling up. It should be noted that while this technique “moves” data from the original file to the new files in the same filegroup, it does not guarantee that all objects residing in the filegroup are “balanced”. Some objects, depending upon their location in the original datafile, may have some data move, all data move or no data move. Ultimately the total amount of allocated pages will be balanced among the various files in the filegroup, but there could still be some hotspots for certain objects. This article and associated script does not attempt to deal with that issue.'...

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/03/split-a-file-group-into-multiple-data-files/?utm_source=SSC&utm_medium=pubemail