Redistributing Data Across Files

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

    Comments posted to this topic are about the item Redistributing Data Across Files

  • DinoRS


    Points: 2676

    It would be nice to mention in such articles that the approach used here only works for tables with clustered indexes, once you're down to heaps with indexes you would have to rebuild both indexes and table something like this:


    ALTER INDEX IDX_Contacts on dbo.Contacts REBUILD
    ALTER TABLE dbo.Contacts REBUILD

    you can verify both table and Index Filegroups afterwards for correct movement / redistribution.

  • n.ryan


    Points: 2236

    Nice description of a useful but not as simple as it appears topic. Thanks.


    Minor thing, the image with the caption "There has been a little movement, as my new files are 90% full (roughly) and the old one is 43% full. However, that's not great" is the same as the image above and doesn't show 43% full.

  • Thomas Franz

    Hall of Fame

    Points: 3647

    theoretical you could have added one more file to the filegroup and used

    DBCC SHRINKFILE (N'<original file>' , EMPTYFILE)

    to move all data into the new files (will be evenly distributed in this case.

    Drawback: it can take VERY long when it is a big file / filegroup and even in I read somewhere, that I could cancel / kill the DBCC-session, it seems to rollback the whole move sometimes (at least on SQL 2016).

    Benefit: usually your original file is e.g. 400 GB (current size) but you do not want to have four 400 GB files that are filled only to 25 % (I guess everyone favors four 100 GB files). When you just rebuild Indexes (or even create copies of the tables and move data into them, e.g. because you want to add partitioning), you will end with 400 GB + 100 GB + 100 GB + 100 GB, where about 4/7 of the data remains in the first file and 1/7 in each of the three new files (because it redistributes it depending on the free space in a file).

    If you would add four 400 GB files and rebuild indexes etc., the data would be distributed equally and you can (usually) shrink the three new files to 100 GB using the TRUNCATEONLY option for DBCC SHRINKFILE, but not the original file, since there is a high chance, that even some pages in the last 100 GB of the file are or were in use.

    But when you add four complete new files, you could just delete the clumsy old one after the EMPTYFILE...


    God is real, unless declared integer.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

    Re: Adding a new filegroup, that's a fairly big decision for most people. If you have 400GB, that may make more sense, but it really depends on what you are aiming for. I didn't mention that because this is a focused article on rebalancing a filegroup, not managing a large database set.

    Good general recommendations are to have separate filegroups from Primary and be sure you use those to balance out your workload where appropriate. These days with large SANs and LUNs, you might not do as much of that as in the past.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

    Updated the verbiage for the initial rebalance and mentioned that this is for clustered indexes.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply