Too many Partitions, removing older years

Question

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.

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

Answer 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.