Post reply

Data Allocation among multiple files in the same file group

  • September 13, 2008 at 6:47 pm

    #212571

    I have 1 SQL database, with 2 data files: "Data1" on the E drive and "Data2" on the F drive, both in the primary filegroup.

    Data1 has autogrowth=none and has 500 Meg free.

    Data2 has 100 Meg free space, and can grow another 3 Gig before hitting maxsize.

    If neither one is full, and SQL needs to add rows to a table currently in Data1, does SQL try to add to Data1 ? Or is it random ? Or more complicated ?

  • September 14, 2008 at 5:53 am

    #871527

    SQL will try to balance the content of files in the same filegroup.

    If you want to take that under your control, you should create your own filegroups and move objects overthere.

    Each file group can have multiple files.

    It is best to keep all files of a filegroup of the same size (because of the balancing ).

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 14, 2008 at 8:29 am

    #871543

    Data from one table usually sticks in the same filegroup, not sure about the same file.

  • September 14, 2008 at 10:52 am

    #871553

    There's a proportional fill algorithm that's used. It doesn't matter what the file's max size is, just how much free space is in the files.

    If you have three files in one filegroup, one has 20% free space, one has 15% free space and one has 30% free space, allocations will occur in the one with the 30% free space.

    I'm not sure how SQL picks which file to grow if all have 0% space available

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 3, 2012 at 12:13 pm

    #1427940

    So if that's true.

    Here is my situation I have a Sharepoint databases.

    datafile 1 is 200gb

    datafile 2 is 13gb

    In order for me to force datafile 2 to autogrow more than datafile 1. I need to give it more free space.

Viewing 5 posts - 1 through 4 (of 4 total)

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