December 8, 2023 at 10:26 pm
Hello,
so I was reading on partitioning a large table, Which i successfully did, using the clustered index and partition function/schema... example
the table is a simple table with 4 columns
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
ItemID INT,
OrderDate DATETIME
);
CREATE CLUSTERED INDEX Order_Date_Added_Partition ON dbo.Orders (OrderID)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 85)
ON Orders_Sch(OrderDate)
GO
So I broken the partitions down by Table Name, Year and Month, example:
USE [master]
ALTER DATABASE [Orders]
ADD FILEGROUP [2022_5];
ALTER DATABASE [Orders]
ADD FILE
(
NAME = Orders_2022_5,
FILENAME = 'D:\Data\Orders_2022_5.ndf',
SIZE = 16MB,
MAXSIZE= 128MB,
FILEGROWTH = 128MB
)
TO FILEGROUP [2022_5];
so I have all that ranging from 2016 to 2024... and we only want to keep 3 years worth to present date... anyone have an Idea how we can remove old partitions from X years ago?
December 9, 2023 at 5:21 pm
Not sure I follow your setup - do you have a monthly partition setup for each month going back to 2016? And now you want to remove the partitions older than 3 years (36 months)?
If that is the case, then the process is fairly simple:
Since you have also decided to implement each partition in its own file and filegroup - then you need to add steps to drop the table(s) and drop the filegroup. Personally, I would not implement this in multiple filegroups unless each month contains 100's of GB's of data (page compressed). I might consider a yearly filegroup - but it would have to be a very large table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy