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
CREATE DATABASE SE_DEMO;
–Now Create Five Filegroups 
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange01;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange02;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange03;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange04;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange05;

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

–Now Add One File to Each Filegroup 
ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_01, FILENAME = ‘C:\FGDayRange_01.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange01;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_02, FILENAME = ‘C:\FGDayRange_02.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange02;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_03, FILENAME = ‘C:\FGDayRange_03.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange03;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_04, FILENAME = ‘C:\FGDayRange_04.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange04;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_05, FILENAME = ‘C:\FGDayRange_05.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange05;

USE SE_DEMO
GO 

Step 3
We then create our partition function.

–Now Create a Partition Function
CREATE Partition FUNCTION pfFiveDayRange (Datetime)
AS RANGE RIGHT FOR VALUES
(‘2017-10-01’, ‘2017-10-02’, ‘2017-10-03’, ‘2017-10-04’, ‘2017-10-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 and clustered index on partition key.
CREATE TABLE SE_DEMO_Partitions
(ID INT IDENTITY,
businessDate DATETIME NOT NULL)
ON psFiveDayRange (businessDate);
GOCREATE CLUSTERED INDEX IDXC_businessDate ON SE_DEMO_Partitions(businessDate)

Step 6
We load some sample data.

SET NOCOUNT ON
–Load 50 records with each date
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES (‘2017-10-01’)
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES (‘2017-10-02’)
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES (‘2017-10-03’)
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES (‘2017-10-04’)
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES (‘2017-10-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
SCHEMA_NAME(t.schema_id) AS SchemaName
,OBJECT_NAME(i.object_id) AS ObjectName
,ISNULL(QUOTENAME(i.name),’Heap’) AS IndexName
,p.partition_number AS PartitionNumber
,fg.name AS Filegroup_Name
,rows AS ‘Rows’
,au.total_pages AS ‘TotalDataPages’
,CASE boundary_value_on_right
WHEN 1 THEN ‘less than’
ELSE ‘less than or equal to’
END AS ‘Comparison’
,value AS ‘ComparisonValue’
,p.data_compression_desc AS ‘DataCompression
,p.partition_id
,ps.name AS PartitionScheme
,f.name AS PartitionFunction
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au ON au.container_id = p.partition_id
JOIN sys.tables t ON p.object_id = t.object_id
WHERE OBJECT_NAME(i.object_id) =‘SE_DEMO_Partitions’
ORDER BY ObjectName,i.name,p.partition_number;
GO

Output of above query Something like below:

By |2019-01-27T15:06:31+00:00July 12th, 2018|DBA, Microsoft SQL|

About the Author:

SCALABILITY ENGINEERS PVT. LTD.

WE ARE ONE-STOP SOLUTION FOR YOUR MICROSOFT SQL SERVER MANAGEMENT.

LET’S TALK

CONTACT US

 Adrian Clayborn Authentic Jersey