I have a table that has 54 partitions and is a range right partition function. So ideally I like to have 52 partitions with data in them and the first and the last partitions are empty. Well the other day, our ETL team ran a load job that is meant to run after our weekly load process. They had not run the weekly load process for three weeks prior to running this other job though. This means that I do not have partitions created for dates that they are going to be inserting data for. I looked at my table and I saw that my last end partition, that I like to have 0 rows, had about 15 million rows in it. I contacted them and they told me that since they had not run the weekly job for so long they thought they would just catch up by loading all of it at the same time. Well this is an issue, becuase now if I split my partition on the weekly date that I need to, SQL Server will have to re-sort all that data into the correct partitions. This is not a quick operation. So I had to do some clean up.
This is fairly easy to do since I did not split my partition yet.
1. Create a stage out table with the same structure, indexes included, as the base table and make sure it is on the same filegroup.
2. Swithc that last partition out to the stage table that you just built.
3. Drop the stage table.
Now all that data is gone and my partitions looked good again. Now I asked the ETL team to run our standard weekly process to catch up and they did. Now all is well with my table.