Manage multiple partitions in multiple filegroups in SQL Server for cleanup purposes
SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL 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.
This solution talks about how to manage old partitions without creating a NON PARTITIONED table in each filegroup. If you are not dealing with multiple filegroups, you can refer to this other tip Switching data in and out of a SQL Server 2005 data partition since that tip talks very clearly about how to manage partitions if you have only one filegroup such as the PRIMARY filegroup..
Considering, in a real heavy loaded environment, you might have multiple filegroups within a partition scheme, it is not feasible to create NON partitioned table on every filegroup for the cleanup purpose. This tip shows how you can switch multiple partitions by creating another partitioned table using the same Partition Scheme as the original table.
To start with, let's create a sample database which contains several filegroups for our demo purposes.
Now let's create the files for each filegroup.
We then create our partition function.
Then we create our partition scheme.
We create a new partitioned table.
We load some sample data.
Here we check to see how the data was loaded and how many rows are in each partition.
Now you want to clean up the partitions for dates 2008-09-01, 2008-09-02, and 2008-09-03. The traditional way to do so is by creating three NON PARTITIONED tables in each filegroup FGDayRange01, FGDayRange02, FGDayRange03 and switching each partition to the corresponding NON PARTITIONED table. Now, you can forget about doing all that and do it all in one step by creating another PARTITIONED TABLE using the same partition scheme as the original table.
Now you can switch all partitions within one table since the partition scheme takes care of the physical layout of the structure.
Run a count to see that data has been switched to the clean up table.
Now the three dates from the original table have been switched to the cleanup table. Now you can simply truncate the cleanup table.
Now you are all done. You can merge partitions in the original table or reload the table with the correct data or do anything else you wish. I hope you all enjoyed the idea of not creating multiple non-partitioned tables to hold the partitions for switch command.
- You can read more about Partitioned Tables and Indexes by reading this white paper SQL Server 2005 Partitioned Tables and Indexes
- You can go to SQL Server BOL and read more on the following topics
- $Partition Function
- CREATE PARTITION SCHEME
- CREATE PARTITION FUNCTION
- Sys.Partition% system table/views
Last Updated: 2008-09-10
About the author
View all my tips
- Handling Large SQL Server Tables with Data Partiti...
- Identify Overloaded SQL Server Partitions...
- Manage multiple partitions in multiple filegroups ...
- Partitioning SQL Server Data for Query Performance...
- SQL Server Database Partitioning Myths and Truths...
- Switching data in and out of a SQL Server 2005 dat...
- More SQL Server DBA Tips...