Manage multiple partitions in multiple filegroups in SQL Server for cleanup purposes

By:   |   Updated: 2008-09-10   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Partitioning

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_01FILENAME 'C:\FGDayRange_01.NDF', SIZE 5MBFILEGROWTH 1MB
)
TO FILEGROUP FGDayRange01

ALTER DATABASE MSSQLTIPS
ADD FILE 
(
    
NAME FGDayRange_02FILENAME 'C:\FGDayRange_02.NDF', SIZE 5MBFILEGROWTH 1MB
)
TO FILEGROUP FGDayRange02

ALTER DATABASE MSSQLTIPS
ADD FILE 
(
    
NAME FGDayRange_03FILENAME 'C:\FGDayRange_03.NDF', SIZE 5MBFILEGROWTH 1MB
)
TO FILEGROUP FGDayRange03

ALTER DATABASE MSSQLTIPS
ADD FILE 
(
    
NAME FGDayRange_04, FILENAME 'C:\FGDayRange_04.NDF', SIZE 5MBFILEGROWTH 1MB
)
TO FILEGROUP FGDayRange04

ALTER DATABASE MSSQLTIPS
ADD FILE 
(
    
NAME FGDayRange_05FILENAME 'C:\FGDayRange_05.NDF', SIZE 5MBFILEGROWTH 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(businessDateAS [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(businessDateAS [Partition Number]
FROM MSSQLTIPS_Partitions 
GROUP BY $Partition.pfFiveDayRange(businessDate); 

SELECT COUNT(*) AS [Total Records]$Partition.pfFiveDayRange(businessDateAS [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


Last Updated: 2008-09-10


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, December 06, 2012 - 9:49:07 PM - tungtt Back To Top

Hi Nitin!  Currently, I have a database. In one day I added about 20 million records. Can you help me solve this problem? I desperately needed it. Thanks


Thursday, May 17, 2012 - 3:15:02 AM - Chenthil Back To Top

Can you use this option to do archiving of partition tables to a different instance and remove data from orginal transaction instance...

 


Sunday, May 13, 2012 - 10:06:24 PM - Nitin Rana Back To Top

Yes Jorge: 

It is possible. Sorry for the extremely delayed reply as I never got notified about your question in the email. What you can do is either create all the filegroups in advance for one year so rather than managing them every month, you only manage them once a year. OR you can also write a program that will run on first of every month that will add a filegroup for you. Usually, adding a filegroup on 1st of every month can be challenging if you have a system that has continuous load of data happening 24*7. You will be better off having the set up done in advance so you dont have to worry about it every month.

Hope this helps. Otherwise, please post your question again with exact specifics and desired result.

Nitin 

[email protected]


Tuesday, April 03, 2012 - 3:02:03 PM - Jorge Back To Top

Hi Nitin, very good article. you know if it's possible to make this solution dinamically?

Example: I need to hold very large of data into a table called Table1 which has a datetime field and need to create multiple filegroups (one per month) dinamically. When a row is inserted at the begining of March the database should create a new filegroup holding data belonging to this month and so on.

Is it possible?

Thanks, Jorge



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools