Post reply

Multiple Data Files

  • November 14, 2013 at 1:09 am

    #395820

    I have a db that has 3 data files. The first data file is on one drive and the remaining two on another. All three were set to autogrow with no limit on maxsize.

    My understanding is that data is spread evenly across all three data files? So as the database grows data is placed evenly across all three data files and each file would autogrow as required until maxsize is reached. However, I noticed that the third file had not really grown which surprised me? The first two were set to the same initial size but the third was set to a much smaller size - would this have caused that?

    Also, should there not be a limit set on the max size of each file to avoid filling a drive, particularly on the drive with two data files?

    Thanks

    Steve

  • November 14, 2013 at 1:38 am

    #1666281

    Are they all in the same filegroup?

    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

  • November 14, 2013 at 9:16 am

    #1666430

    shindle 17293 (11/14/2013)

    I have a db that has 3 data files. The first data file is on one drive and the remaining two on another. All three were set to autogrow with no limit on maxsize.

    My understanding is that data is spread evenly across all three data files? So as the database grows data is placed evenly across all three data files and each file would autogrow as required until maxsize is reached. However, I noticed that the third file had not really grown which surprised me? The first two were set to the same initial size but the third was set to a much smaller size - would this have caused that?

    Also, should there not be a limit set on the max size of each file to avoid filling a drive, particularly on the drive with two data files?

    Thanks

    Steve

    Can you post results from the following query please

    select d.name, f.name from sys.database_files d

    inner join sys.filegroups f on d.data_space_id = f.data_space_id

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 14, 2013 at 4:06 pm

    #1666576

    Yes they are all in same filegroup.

    After doing some more research I discovered that the data files are filled by the same amount if each file is the same size (round robin fashion) which can improve performance. However, if one or more of the files is larger than the other(s) then this will be filled by a larger % than the smaller one(s).

    In terms of the autogrow feature each file will just grow as per the setting specified when required.

    The maxsize is also a setting that doesn't really impact this process - when the maxsize is reached that file will not grow but the others will continue to if space is available.

    Please let me know if there is anything I've misunderstood.

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

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