Drop or delete a existing partition

Question

Post reply

Drop or delete a existing partition

Siten0308

SSCarpal Tunnel

Points: 4404

December 8, 2023 at 10:26 pm

#4342486

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?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 1

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:

Identify the month for the oldest partition
Check if that month is less than the data retention range - if yes:
1. TRUNCATE TABLE {table} WITH (PARTITION (1)); -- partition 1 will always be the oldest
2. ALTER PARTITION SCHEME {partition scheme} NEXT USED [filegroup]
3. ALTER PARTITION FUNCTION {partition function} MERGE RANGE (oldestmonth)
Repeat until you reach your desired retention period
Setup an agent job to run once a month to create the next months partition and remove oldest partition

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