sql filegroups  : 3 out of 4 files to a file group has 0 growth?

Posted on 2011-05-11
Last Modified: 2012-05-11
       I have 4 files in a file group, each of them are 50 MB. now 3 of them have zero (0) growth.
what is the best practice ?

solution: 1. can i increase the size of all the 3 files alter database........or via management studios.
solution  2: increase the size of just 2 files?
solution 3 : add another file. to the filegroup.
solution 4:  ..something else.

What will be the effect of the different solutions?
Question by:shanj
    LVL 14

    Expert Comment

    SQL Server uses proportional fill algorithm which distributes data proportionally across files in filegroup.
    Using Files and Filegroups
    Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
    As soon as all the files in a filegroup are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically. For example, a filegroup is made up of three files, all set to automatically grow. When space in all the files in the filegroup is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on.

    According to above, for maximum performance, it's best to set same size and growth for all files of a filegroup

    >I have 4 files in a file group, each of them are 50 MB. now 3 of them have zero (0) growth.
    >what is the best practice ?

    It depends on your needs, if you decide to store new data only in one file in that filegroup, which has autogrowth enabled you can stay on what you have now. When you decide otherwise (you want to add new data to other files also) I'd suggest to increase size to equal values. Also autogrowth of each file enabled to grow set to equal values (each file has same autogrowth value).

    Author Comment

    Sorry i do not have autogrowth, it is fixed.

    LVL 14

    Accepted Solution

    You can grow all of them by the same value if you wish to allow data to be written to them.

    Author Closing Comment

    I did not solve it 100% but i found another way.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now