I have 9 weekly partitions on one filegroup. How many files do I need to create?

There are 3 tables containing 46 million, 16 million and 10 million rows.

  • Are you talking about table partitioning?? If so, why are you putting all partitions on a single filegroup? You're eliminating one of the most powerful parts of table partitioning (having filegroups on different disks, and/or make cold partitions read-only, etc.). Jun 3, 2012 at 16:52
  • yes,cause the switching partition I need the staging table on the same filegroup and to avoid the drop and create staging table, prevent fragmentation, I need all partitions on one filegroup.
    – wispa2001
    Jun 3, 2012 at 16:55
  • You haven't provided nearly enough information to properly answer this question... What is the size of the data? What are the access patterns? What I/O subsystem is backing the database? How much RAM is allocated to the server? etc...
    – Jon Seigel
    Jun 3, 2012 at 22:24
  • DB size is 400GB. RAM is 12 GB. Subscription server executes a dump every 5 minutes and the resulting file is ~1GB.
    – user9208
    Jun 4, 2012 at 5:41

1 Answer 1


You need one file.

Unless you have specific requirements to have multiple files, you only need one file for the filegroup. The 'specific' requirements would be things like requirement to spread the filegroup on multiple disk locations or a requirement for round-robin allocation. You did not mention any such requirement, you only give information about the number of rows in the tables, which is largely irrelevant when it comes to deciding the number of files in the filegroups.

  • the problem is that to correct manage partitioned table I need to create for every partition the filegroup with one file. But now for the fragmentation issue I cannot manage it in this way. And I need to know what is the correct way to create file for a lot of data.
    – wispa2001
    Jun 3, 2012 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.