1

This is a follow-on to this question a few years ago Splitting Existing Partitions in SQL 2014 where now there are partition years for 2011 all the way to 2021.

I need to archive partitions 2011 to 2015 by moving the data from these partitions to a new table/database, and then archive this database, and remove the partitions for 2011 to 2015 from the original Log table.

I'm successfully moving the data out to a Log_Archive table with the following statement.

ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20120101')
    TO dbo.Log_Archive PARTITION $PARTITION.pfYearlyPartition_Log_Archive('20120101');

What I'm not sure of is how I now remove that partition from the Log table, as it's still there but with 0 rows within it. Can someone advise? You can see below I've successfully moved the data from Log to Log_Archive but I'm left with 0 rows in the original Log table that I want to remove.

enter image description here

I'm hoping I don't need to re-partition the whole table as it's very large.

1 Answer 1

1

Once the partition is empty and no other partitioned table on a partition scheme using the same partition function has data in the corresponding partition, just use ALTER PARTITION FUNCTION to MERGE away boundary point in the empty partition.

1
  • Thanks David, appreciate the reply.
    – Philip
    Commented Mar 31, 2021 at 12:25

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.