File & File Group Fill Strategy

Hi, Recently I faced one situation, i.e., my database is having two data files and each data file is there is different drives and each drives are having almost same space. On week end application team did some transaction on my database so it started growing, and I can see only one drive is getting full but another drive didn't, it still have lot more free space than drive one. I was surprised, two data files are placed on each drive but only one drive is getting full. Later I realized this was because of initial size of a data file was set to two different sizes.

• File groups use proportional fill strategy across all the files within each file group.
• As data is written into the file group, SQL Server database engine writes an amount proportional to the free space in the file to each file with in the file group, Instead of writting all the data to first file until full, It then writes to the next file.
• For ex, if file f1 has 100mb free and file f2 has 200mb free space, Once extent is allocated for f1, two extents for f2 and so on.
• In this way both files became full at about the same time.
• As soon as all the file in the file group are full, the database engine automatically expands on file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically.
• For ex. a file group is made up of three files, all set to automatically grow.
• When space in  all files in the file group is exhausted, only the first file is expanded.
• When the first file is full and no more data can be written to the file group, the second file will expand
• When the second file is full and no more data can be written to the file group, the 3rd file is expanded. If 3rd file is full and no more data can be written to the file group, the first file is expanded again, and so on.

Hope this info is useful to you...