Partition table design and house keeping (MSSQL)

All we need is an easy explanation of the problem, so here it is.

I’m new to design Partition table, So my question is

  1. If I need to daily partition and keep the back up for 6 months, Should I create only one file group or create file group per day?
    which should be best practice because create file daily would make up to 180 file per table.

  2. Can I use round robin in this case? eg. create only 7 file group

3.On question 1. if the daily file is the right answer can you show some example of store procedure code for this case? (I have only example of dynamic assign to only one file group, and I’m not that good in store procedure coding to be honest)

Thank you very much,

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Unless you have a specific need to do otherwise, I suggest you use a single filegroup for all partitions. This will simplify partition maintenance and avoid unnecessary complexity. You can use either the primary file group or a user-defined one for this purpose.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply