1

(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?

3
  • Are you using SQL Server Enterprise? Because I believe you could use Resource Governor to control and limit how much resource should be used to process this request.
    – Ronaldo
    Mar 11, 2020 at 1:21
  • 1
    It turns out that the DBA team had already started testing rebuild of tables into multiple files. I'm hearing that they're having difficulties. Mar 16, 2020 at 15:28
  • The summary of issue that I've received is around dealing with LOB fields on a rebuild. Mar 20, 2020 at 17:16

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.