Post reply

Creating additional data file for a particular file group - Anything needs to be checked out?

  • July 6, 2015 at 1:02 pm

    #298325

    For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.

    Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?

    What are the best practices I should follow.

    Please share your thoughts.

    Thanks!

  • July 6, 2015 at 1:39 pm

    #1810563

    Why are you worried about 70GB files growing 'too much'?

    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

  • July 6, 2015 at 1:47 pm

    #1810566

    Thanks Gail.

    I was thinking if it's a best practice not to allow the file to grow too much and I can split across separate drives/spindles. This particular db is almost 450gb in size.

  • July 6, 2015 at 1:51 pm

    #1810568

    SQL!$@w$0ME (7/6/2015)

    I was thinking if it's a best practice not to allow the file to grow too much

    That's not a best practice, you'll really likely only have issues when the files are Terrabtyes in size not Gigabytes, even then they're fully supported

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

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

  • July 6, 2015 at 2:01 pm

    #1810570

    SQL!$@w$0ME (7/6/2015)

    This particular db is almost 450gb in size.

    So a fairly small database still.

    SQL data files max out at 16 TB, not a few GB. You split across drives (separate IO paths) if you're having IO contention or have inadequate IO throughput, not because the file has reached some arbitrary (smallish) size.

    And if you are splitting for IO load purposes, adding a new file is the worst thing you could do, as due to proportional fill, all writes would go to the new file initially.

    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

  • July 6, 2015 at 2:50 pm

    #1810581

    Thanks Gail/Perry.

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

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