Post reply

How the data will be distributed to the secondary file?

  • August 2, 2010 at 4:42 pm

    #222105

    We have a database of size 60 GB

    Mdf file size = 50 GB (used space 26GB) on Drive D

    ldf fil size = 10 GB on drive E

    Now after 6 months, we added a secondary data file .ndf of size 50 GB in the same file primary file group on drive F.

    So my question is how the data will be distributed to secondary? Is the data will be written only to the secondary file .ndf until it's used space comes equal to Primary data file's used space (i.e 26 GB in our case)? and then starts filling primary & secondary in equal proportion?

    How this exactly works?

    The secondary file is created with an intention to increase the IO performance as we have 8 processors and want distribute the IO to at least 3 drives (1 mdf, 1 ndf and 1 log file).

    Thanks

  • August 2, 2010 at 11:28 pm

    #1201355

    SQL uses a proportional fill algorithm where the amount of data written to the files in the filegroup is proportional to the amount of free space in the files.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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