[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-05-11
Medium Priority
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
  • 2
  • 2
LVL 14

Expert Comment

ID: 35738068
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

ID: 35740932
Sorry i do not have autogrowth, it is fixed.

LVL 14

Accepted Solution

Daniel_PL earned 1500 total points
ID: 35741651
You can grow all of them by the same value if you wish to allow data to be written to them.

Author Closing Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

872 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