Share via

How does large on-prem SQL database with multiple files fill up

Kumar 1 Reputation point
2024-04-30T07:00:47+00:00

I got a large database with one filegroup having multiple data files spread across 2 disks. But one of the 2 disks is larger than other, with all data files on both disks have Autogrowth disabled, and the value "is_autogrow_all_files" is set to 1 on the filegroup.

The data files on larger disk are set to 146GB (initial size) with about 129GB used space in them, while the data files on smaller disk are set to 126GB with about 124GB used space in them.

How do these data files expected to fill-up if we add large amount of data ?

Will all the data files fill-up at balanced rate until the smaller data files fill-up are full and then the larger data files fill-up until they are full. Or smaller data files stop filling at somepoint and let the larger data files fill up to a point, after which they will all fillup until they are full ?

If I add some additional data files to the same filegroup on a separate disk, when will these new data files start filling up ? Will database try to move existing data from existing data files to the new data files to try and rebalance free space or any new data will be first written to new data files until they are of same size as older datafiles, then it will try to maintain balance on all data files ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.1K Reputation points MVP
    2024-04-30T21:25:29.5133333+00:00

    No, SQL Server will not start to move data around just to rebalance - moving lots of data is expensive. But if you rebuild an index, the new version of the index may appear in one of the new files.

    As Olaf and Lucy said, SQL Server writes to the files in a round-robin fashion. Therefore, the best if the files have the same size and autogrow settings.

    What will happen if you add completely new files I don't know. But it sounds corny, if would insist on doing round-robin over files that are full and cannot grow.

    I would recommend that you restore a backup of the database on an idle machine, add the new files, and then add some load on the database to see what happens.

    1 person found this answer helpful.
    0 comments
  2. Olaf Helper 41,006 Reputation points
    2024-04-30T07:07:49.6+00:00
    0 comments
  3. LucyChenMSFT-4874 1,280 Reputation points
    2024-04-30T08:25:05.9366667+00:00

    Hi @Kumar,

    Thanks for your information.

    How do these data files expected to fill-up if we add large amount of data?

    I've checked the official document; we can get the information:

    Filegroups use a proportional fill strategy across all the files within each filegroup.

    If you want to learn more about the SQL Server Proportional fill algorithm, I found this article explain it in great detail, hope this can help you understand well.

    In addition, you can follow the steps in this article to add data files to a database.

    Feel free to share your issue here.

    Best regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications