HELLO 2015...
I will use the following Partition Scheme and Function examples. I have bolded the partition scheme and function which I need to remove.
Partition Scheme
CREATE PARTITION SCHEME [TestPartitionScheme] AS PARTITION [YYYYMMPartitionFunction] TO ([FG1], [FG2011], [FG2011], [FG2011], [FG2011], [FG2011], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2015])
GO
Partition Function
CREATE PARTITION FUNCTION [TestPartitionFunction](date) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2011-02-01T00:00:00.000', N'2011-03-01T00:00:00.000', N'2011-04-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-02-01T00:00:00.000', N'2012-03-01T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-05-01T00:00:00.000', N'2012-06-01T00:00:00.000', N'2012-07-01T00:00:00.000', N'2012-08-01T00:00:00.000', N'2012-09-01T00:00:00.000', N'2012-10-01T00:00:00.000', N'2012-11-01T00:00:00.000', N'2012-12-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2013-02-01T00:00:00.000', N'2013-03-01T00:00:00.000', N'2013-04-01T00:00:00.000', N'2013-05-01T00:00:00.000', N'2013-06-01T00:00:00.000', N'2013-07-01T00:00:00.000', N'2013-08-01T00:00:00.000', N'2013-09-01T00:00:00.000', N'2013-10-01T00:00:00.000', N'2013-11-01T00:00:00.000', N'2013-12-01T00:00:00.000', N'2014-01-01T00:00:00.000', N'2014-02-01T00:00:00.000', N'2014-03-01T00:00:00.000', N'2014-04-01T00:00:00.000', N'2014-05-01T00:00:00.000', N'2014-06-01T00:00:00.000', N'2014-07-01T00:00:00.000', N'2014-08-01T00:00:00.000', N'2014-09-01T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-11-01T00:00:00.000', N'2014-12-01T00:00:00.000', N'2015-01-01T00:00:00.000')
GO
In the example, Range Right Partition Function keeps data earlier than 2011-01-01T00:00:00.000 (Lowest boundary) will go to the file group FG1 . In Data warehouse database, I have created a view which will show me tables which are partitioned and their file groups. I gave credit to Kendra Little from
CREATE VIEW [dbo].[FileGroupDetail]
AS
SELECT pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
ds.name AS partition_filegroup ,
pf.type_desc AS pf_type_desc ,
pf.fanout AS pf_fanout ,
pf.boundary_value_on_right ,
OBJECT_NAME(si.object_id) AS object_name ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
ELSE 0
END) AS num_rows ,
SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,
SUM(CASE ISNULL(si.index_id, 0)
WHEN 0 THEN 0
ELSE 1
END) AS num_indexes
FROM sys.destination_data_spaces AS dds
JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
GROUP BY ds.name ,
p.partition_number ,
pf.name ,
pf.type_desc ,
pf.fanout ,
pf.boundary_value_on_right ,
ps.name ,
si.object_id ,
rv.value;
GO
--------------------
Now I want to know which table are partitioned and number of rows. I ran the following scripts
select
partition_filegroup,
object_name,
sum(num_rows)
from ph.FileGroupDetail
where partition_filegroup in('FG2011')
group by partition_filegroup,object_name
To find out how many partition I need to remove from the partition.
select * from dbo.FileGroupDetail
where object_name = 'Table Name' and partition_filegroup = 'FG2011'
I am following Kendra Little video and built switch out tables for each partitioned table. In this example, we have 4 partition
Create a switch out table in the file group which you need to drop
CREATE TABLE [TEST_SWITCHOUT](
[CHTIDDD] [decimal](3, 0) NOT NULL,
[CHSEQQ] [decimal](5, 0) NOT NULL,
[CHTYPEE] [char](3) NOT NULL,
)
ON [FG2011]
GO
SWITCH OUT Script for each partition of the table
RAISERROR ('Switching out.',0,0)
ALTER TABLE TEST
SWITCH PARTITION 2 TO dbo.TEST_SWITCHOUT;
GO
Truncate table dbo.TEST_SWITCHOUT
Go
RAISERROR ('Switching out.',0,0)
ALTER TABLE TEST
SWITCH PARTITION 3 TO dbo.TEST_SWITCHOUT;
GO
Truncate table dbo.TEST_SWITCHOUT
Go
RAISERROR ('Switching out.',0,0)
ALTER TABLE TEST
SWITCH PARTITION 4 TO dbo.TEST_SWITCHOUT;
GO
Truncate table dbo.TEST_SWITCHOUT
Go
Drop table dbo.TEST_SWITCHOUT
Go
MERGE BOUNDARY POINTS
We are ditching the following months which we want to remove.
--2011
ALTER PARTITION FUNCTION TestPartitionFunction()
MERGE RANGE ('2011-01-01T00:00:00.000')
ALTER PARTITION FUNCTION TestPartitionFunction()
MERGE RANGE ('2011-02-01T00:00:00.000')
ALTER PARTITION FUNCTION TestPartitionFunction()
MERGE RANGE ('2011-03-01T00:00:00.000')
ALTER PARTITION FUNCTION TestPartitionFunction()
MERGE RANGE ('2011-04-01T00:00:00.000')
REMOVE FILE GROUP and FILE
Use YourDatabase
Alter Database YourDatabase Remove File FG2011
go
USE [YourDatabase]
GO
ALTER DATABASE [YourDatabase] REMOVE FILEGROUP [FG2011]
GO
IF you have this error, run the script to see which object is still in the file group.
the file cannot be removed because it is not empty
select * from sys.partitions p
inner join sys.allocation_units a on a.container_id = p.hobt_id
inner join sys.filegroups f on f.data_space_id = a.data_space_id
where f.name='FG2011'
select * from sys.objects
where object_id = '949578421'
No comments:
Post a Comment