Remove or Archive Old Partitions when we have multiple partitions in multiple filegroups in SQL Server.

Scenario:

Remove old partitions from a partition table, you should either issue a Delete or Switch command. BOL (Books Online) also suggests that while Switching partitions, you should set up a Non-Partitioned Table in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming.  However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.

Solution:

You might have multiple filegroups within a partition scheme, it is not feasible to create NON-partitioned table on every filegroup for the Archiving\Purging purpose. This tip shows how you can switch multiple partitions by creating another partitioned table using the same Partition Scheme as the original table.

Step 1

To start with, let’s create a sample database which contains several filegroups for our demo purposes.

–Create the Database

Copy to Clipboard

–Now Create Five Filegroups
Copy to Clipboard

Step 2
Now let’s create the files for each filegroup.

–Now Add One File to Each Filegroup

Copy to Clipboard

Step 3
We then create our partition function.

–Now Create a Partition Function

Copy to Clipboard

Step 4
Then we create our partition scheme.

–Now create a Partition Scheme to Hold Five Filegroups
–These Five Filegroups will hold the data for the five dates defined in Partition Function.

Copy to Clipboard

Step 5
We create a new partitioned table.

–Now create a Partitioned Table and clustered index on partition key.

Copy to Clipboard

Step 6
We load some sample data.

Copy to Clipboard

Step 7
Here we check to see how the data was loaded and how many rows are in each partition.

Copy to Clipboard

Output of above query Something like below: