All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂