Drop or delete a existing partition

• Siten0308

  SSCarpal Tunnel

  Points: 4409

  More actions

  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?

   

• Jeffrey Williams

  SSC Guru

  Points: 90349

  More actions

  December 9, 2023 at 5:21 pm

  #4342894

  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:

  1. Identify the month for the oldest partition
  2. 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)

  3. Repeat until you reach your desired retention period
  4. 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