(Manually moving this from StackOverflow, as it really belongs here.)
We have a production SQL Server Enterprise database that has a few TB of data in it. We have a need to split the data into multiple files. Tests in pre-Production environments have gone well with the exception that during the shrink the performance of the database is degraded. We would like to get ahead of potential issues with shrink/split of files in the production system, since we have large customers with SLA's that limit our ability to make changes that significantly affect performance.
Does the SQL Server shrink/split functionality have the ability to limit how much overhead the shrink/split incurs during the transition to multiple files? Or, do we have paths that enable the same limiting ability. Alternately, can an approach be taken that gradually splits/shrinks the files during given maintenance windows?
Best Answer
You don't need to shrink to migrate data to multiple files.
Simply add the new files tot the filegroup, then rebuild each table one-by-one to move the data around.
The proportional fill algorithm will take care of equally balancing the data between the files, assuming you size the files in an optimal way. See this Microsoft Docs page for details.
Use the
ALTER TABLE ... REBUILD;command to migrate the data across all available files in a filegroup. You can even use that command to migrate a table to another filegroup. Microsoft Docs for that command are here