Managing multiple partitions in multiple filegroups in SQL 2005 for cleanup purposes
Written By: Nitin Rana -- 9/10/2008
-- read/post comments
-- print --
Rating:
Rate
Problem
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.
Solution
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.
Step 1
To start with, let’s create a sample database which contains several filegroups for our demo purposes.
--Create the Database
CREATE DATABASE MSSQLTIPS;
--Now Create Five Filegroups
ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange01;
ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange02;
ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange03;
ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange04;
ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange05; |
Step 2
Now let's create the files for each filegroup.
--Now Add One File to Each Filegroup
ALTER DATABASE MSSQLTIPS
ADD FILE
(
NAME = FGDayRange_01, FILENAME = 'C:\FGDayRange_01.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange01;
ALTER DATABASE MSSQLTIPS
ADD FILE
(
NAME = FGDayRange_02, FILENAME = 'C:\FGDayRange_02.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange02;
ALTER DATABASE MSSQLTIPS
ADD FILE
(
NAME = FGDayRange_03, FILENAME = 'C:\FGDayRange_03.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange03;
ALTER DATABASE MSSQLTIPS
ADD FILE
(
NAME = FGDayRange_04, FILENAME = 'C:\FGDayRange_04.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange04;
ALTER DATABASE MSSQLTIPS
ADD FILE
(
NAME = FGDayRange_05, FILENAME = 'C:\FGDayRange_05.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange05;
USE mssqltips
GO |
Step 3
We then create our partition function.
--Now Create a Partition Function
CREATE Partition FUNCTION pfFiveDayRange (Datetime)
AS RANGE RIGHT FOR VALUES
('2008-09-01', '2008-09-02', '2008-09-03', '2008-09-04', '2008-09-05' )
GO |
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.
CREATE PARTITION SCHEME psFiveDayRange
AS PARTITION pfFiveDayRange
TO ( [PRIMARY], [FGDayRange01], [FGDayRange02], [FGDayRange03], [FGDayRange04],
[FGDayRange05] ); |
Step 5
We create a new partitioned table.
--Now create a Partitioned Table.
CREATE TABLE MSSQLTIPS_Partitions
(ID INT IDENTITY,
businessDate DATETIME NOT NULL)
ON psFiveDayRange (businessDate); |
Step 6
We load some sample data.
SET NOCOUNT ON
--Load 50 records with each date
INSERT INTO MSSQLTIPS_Partitions (businessDate)
VALUES ('2008-09-01')
GO 50
INSERT INTO MSSQLTIPS_Partitions (businessDate)
VALUES ('2008-09-02')
GO 50
INSERT INTO MSSQLTIPS_Partitions (businessDate)
VALUES ('2008-09-03')
GO 50
INSERT INTO MSSQLTIPS_Partitions (businessDate)
VALUES ('2008-09-04')
GO 50
INSERT INTO MSSQLTIPS_Partitions (businessDate)
VALUES ('2008-09-05')
GO 50 |
Step 7
Here we check to see how the data was loaded and how many rows are in each partition.
-- By Now you have 250 records (50 Each Date) sitting on each Filegroup.
-- Let's check this out.
SELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number]
FROM MSSQLTIPS_Partitions
GROUP BY $Partition.pfFiveDayRange(businessDate); |
Step 8
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.
--Note that the DDL for the cleanup table must be exactly same as the original table.
CREATE TABLE MSSQLTIPS_Partitions_Cleanup
(ID INT IDENTITY,
businessDate DATETIME NOT NULL)
ON psFiveDayRange (businessDate); |
Step 9
Now you can switch all partitions within one table since the partition scheme takes care of the physical layout of the structure.
ALTER TABLE MSSQLTIPS_Partitions
SWITCH PARTITION $PARTITION.pfFiveDayRange('2008-09-01')
TO MSSQLTIPS_Partitions_Cleanup
PARTITION $PARTITION.pfFiveDayRange('2008-09-01')
ALTER TABLE MSSQLTIPS_Partitions
SWITCH PARTITION $PARTITION.pfFiveDayRange('2008-09-02')
TO MSSQLTIPS_Partitions_Cleanup
PARTITION $PARTITION.pfFiveDayRange('2008-09-02')
ALTER TABLE MSSQLTIPS_Partitions
SWITCH PARTITION $PARTITION.pfFiveDayRange('2008-09-03')
TO MSSQLTIPS_Partitions_Cleanup
PARTITION $PARTITION.pfFiveDayRange('2008-09-03') |
Step 10
Run a count to see that data has been switched to the clean up table.
SELECT COUNT(*) FROM MSSQLTIPS_Partitions -- Should return 100 Records
SELECT COUNT(*) FROM MSSQLTIPS_Partitions_Cleanup -- Should Return 150 Records
-- See Partition Details
SELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number]
FROM MSSQLTIPS_Partitions
GROUP BY $Partition.pfFiveDayRange(businessDate);
SELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number]
FROM MSSQLTIPS_Partitions_Cleanup
GROUP BY $Partition.pfFiveDayRange(businessDate); |
Step 11
Now the three dates from the original table have been switched to the cleanup table. Now you can simply truncate the cleanup table.
TRUNCATE TABLE MSSQLTIPS_Partitions_Cleanup |
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.
Next Steps
- 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
- FILEGROUP
- Sys.Partition% system table/views
- Sys.destination_data_spaces
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
