January 29, 2026 at 3:00 am
I have a table with partition on create_timestamp field.
Though we're storing all data in one file group, the boundary values are defined for each quarter.
I would like to add few filegroups for the next quarters: 2025-04-01, 2025-07-01 and 2025-10-01
I attached file for the partition, filegroup and rows info. With huge number of rows in the table, can someone please suggest me the best efficient way?
January 29, 2026 at 7:09 am
This was removed by the editor as SPAM
January 29, 2026 at 8:06 pm
I'm not 100% clear on what you want to do. Are you looking to put each partition in its own filegroup?
Filegroups are for managing the files on disk for backup/restore or other admin tasks. Paritions are logical separations inside of a file. I did see this: https://www.mssqltips.com/sqlservertip/1621/sql-server-partitioned-tables-with-multiple-filegroups-for-high-availability/, which seems to suggest separate filegroups.
If I were to do this, then I would likely aim for a script that adds a new filegroup and a partition based on my strategy. If I'm doing by year, then I might name the FG as something like <dbname>_fg2004, <dbname_fg2005>, etc. to make sure it's obvious which partitions should be in which one.
January 30, 2026 at 2:52 pm
We are using a partition function based on month number and have filegroups per month.
Best ref is the one Steve mentioned.
You can add filegroups as you want.
You need to map your PARTITION SCHEME to the correct filegroup when activating a new partition.
e.g.
ALTER PARTITION SCHEME [psMonth] NEXT USED [FGPartition_09];
ALTER PARTITION FUNCTION [pfYearMonth]() SPLIT RANGE ( '2026-09-01 00:00:00.000' );
Keep in mind that if you split an active partition, the data is split at the moment alter the function !
We create empty partitions a couple of months up front with small files, but have IFI enabled and take large extends.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply