0

I am just getting into partitioning, and have found quite a few good blogs on how to set up and manage sliding window partitions (the ones I've chosen to follow are at SQL Shack and MSSQLTips). Both of these how-to's are very clear in their instructions, however neither of them discuss how to manage the empty data files that are added to the database when new partitions, filegroups, and files are added.

I have my misgivings about leaving empty database files lying around (to me, it's just sloppy), so I thought of establishing a fixed number of data files for partitioning (12, one for each calendar month) and re-using these files. For example, instead of having a database file named "MyData_2023_02", the file would be named "MyData_Month02" or similar. However, I am not sure how to "recycle" database files for new partition (again, I am very new to partitioning).

Can someone please point me in the right direction on either:

  • cleaning up empty database files after swapping and merging partitions
  • how to recycle database files for new partitions

I'd much rather learn the correct/proper/better way to handle partitioning from the start rather than have to unlearn and re-learn later on.

EDIT: As per Dan Guzman's solution, I attempted to use a single file and a single filegroup for my partitions. Didn't go so well:

ALTER DATABASE MY_DW
ADD FILEGROUP FG_PriceFiles_Sliding_Window

CREATE PARTITION FUNCTION [PF_PriceFiles_MonthlyPartition] (date)
AS RANGE RIGHT FOR VALUES 
('2019-07-01', '2019-08-01', '2019-09-01', '2019-10-01', '2019-11-01', '2019-12-01')
GO

CREATE PARTITION SCHEME PS_PriceFiles_MonthlyPartition
AS PARTITION PF_PriceFiles_MonthlyPartition
TO ('FG_PriceFiles_Sliding_Window', 'Primary')

Msg 7707, Level 16, State 1, Line 9
The associated partition function 'PF_PriceFiles_MonthlyPartition' generates more partitions than there are file groups mentioned in the scheme 'PS_PriceFiles_MonthlyPartition'.

EDIT 2: Turns out my partition scheme was slightly off, and things appear to be working the way I want them to now

CREATE PARTITION SCHEME PS_PriceFiles_MonthlyPartition
AS PARTITION PF_PriceFiles_MonthlyPartition
ALL TO ('FG_PriceFiles_Sliding_Window')
2
  • 2
    Why do you need separate filegroups? Have you considered placing all partitions on PRIMARY or some other filegroup? That will simplify administration unless you have special needs.
    – Dan Guzman
    May 31, 2023 at 13:34
  • @DanGuzman I'm all for whatever makes things easier, the two blog posts I cited just happened to make sense to me (like I said, I'm brand new to partitioning). I just discovered an old post by you on best practices (dbdelta.com/table-partitioning-best-practices), I'll be sure to ready that thoroughly
    – Kulstad
    May 31, 2023 at 14:18

1 Answer 1

3

Although many partitioning examples map partitions to different filegroups, this adds unneeded complexity unless the underling storage is physically isolated and the isolation is done for a specific reason (e.g.. tiered storage, optimize concurrent scans on spinning media, read-only filegroups, etc.).

I don't see a need for separate filegroups given the sliding window scenario in your question so I recommend simply placing all partitions on the same filegroup (e.g. PRIMARY). This practice will keep things tidy without additional code.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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