Partition table design and house keeping (MSSQL)

Posted on

Question :

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,

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *