January 3, 2014 at 9:13 am
Hi There,
I am new to table partition and testing the following example. The problem is I am not able to drop DailyFG1 after switch out. We have the same partition in prod that I will need to delete old data.
IF db_id('PartitionThis') IS NOT NULL
BEGIN
USE master;
ALTER DATABASE [PartitionThis] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [PartitionThis];
END
GO
CREATE DATABASE [PartitionThis]
GO
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 96MB );
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis_log', SIZE = 128MB , FILEGROWTH = 96MB );
GO
;WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1),
Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B),
Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B),
Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B),
Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B),
Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B),
tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 )
SELECT N
INTO ph.tally
FROM tally
WHERE N <= 100000;
GO
DECLARE @StartDay DATE=
DATEADD(dd,-63,CAST(SYSDATETIME() AS DATE));
CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))
AS RANGE RIGHT FOR VALUES
(@StartDay,DATEADD(dd,30,@StartDay), DATEADD(dd,61,@StartDay),
DATEADD(dd,92,@StartDay), DATEADD(dd,120,@StartDay) );
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2
GO
DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);
SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)
FROM sys.database_files WHERE name='PartitionThis';
WHILE @i <= 2
BEGIN
SET @sql=N'ALTER DATABASE PartitionThis ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',
filename=''' + @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',
size=128MB, filegrowth=96MB) TO FILEGROUP DailyFG'+CAST(@i AS NCHAR(1))
--show the command we're running
RAISERROR (@sql,0,0)
--run it
EXEC sp_executesql @sql;
SET @i+=1;
END
GO
if OBJECT_ID('OrdersDaily','U') is null
CREATE TABLE OrdersDaily (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on DailyPS(OrderDate)
GO
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-80,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Robot' WHEN t.N % 4 = 0 THEN 'Badger' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 1000;
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-59,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Robot' WHEN t.N % 4 = 0 THEN 'Badger' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 1000;
--Two days ago = 2000 rows
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-30,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Flying Monkey' WHEN t.N % 4 = 0 THEN 'Junebug' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 2000;
--Yesterday= 3000 rows
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-01,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 2 = 0 THEN 'Turtle' WHEN t.N % 5 = 0 THEN 'Eraser' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 3000;
--Switch Out tables
CREATE TABLE OrdersDailySwitchOut_p1 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG1];
GO
CREATE TABLE OrdersDailySwitchOut_p2 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG1];
GO
CREATE TABLE OrdersDailySwitchOut_p3 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG1];
GO
--Switch out
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 1 TO OrdersDailySwitchOut_p1;
GO
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 2 TO OrdersDailySwitchOut_p2;
GO
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 3 TO OrdersDailySwitchOut_p3;
GO
--Merge
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-12-01 00:00:00.000' )
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-11-01 00:00:00.000' )
--Drop Staging table
DROP TABLE OrdersDailySwitchOut_p1;
DROP TABLE OrdersDailySwitchOut_p2;
DROP TABLE OrdersDailySwitchOut_p3;
GO
--Remove File succesfully.
USE partitionthis
ALTER DATABASE partitionthis REMOVE FILE DailyF1
Go
--PROBLEM-- filegroup is not empty
ALTER DATABASE partitionthis REMOVE FILEGROUP DailyFG1
Did I miss something ?
Thank you for reply.
AyeMya
January 3, 2014 at 9:20 am
January 3, 2014 at 9:34 am
Here is Original DailyPS
CREATE PARTITION SCHEME DailyPS
AS PARTITION DailyPF
TO (DailyFG1,DailyFG1,DailyFG1,DailyFG2,DailyFG2,DailyFG2);
After I merge boundary points the partition scheme is
***** Object: PartitionScheme [DailyPS] Script Date: 1/3/2014 11:31:53 AM ******/
CREATE PARTITION SCHEME [DailyPS] AS PARTITION [DailyPF] TO ([DailyFG1], [DailyFG2], [DailyFG2], [DailyFG2])
GO
It is still referring to DailyFG1.
January 6, 2014 at 8:48 am
Looks like you were following Kendra Little's examples[/url]. These are excellent examples, but note that she talks about having two empty partitions at the beginning and end of your data. You have run into why you should have those. She also talks about creating some helper functions and I recommend you create those to help you see the issue. Run this code after you merge the range and see that DailyFG1 is still used as the far left partition:
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-12-04 00:00:00.000' )
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-11-04 00:00:00.000' )
SELECT *
FROM ph.FileGroupDetail;
GO
You need to have an empty partition for the far left boundary that won't have data. I would suggest that you create a third partition and leave partition #1 empty so that you don't have this issue in the future. Also, if you haven't already please watch Kendra's videos that go with the scripts to better understand the concepts that I have written about. Here is your example modified to add a third partition and you will notice that I have successfully removed FG2 with the switched out data. You will have to alter the code to make it work for your case, but it should help get you what you need:
IF db_id('PartitionThis') IS NOT NULL
BEGIN
USE master;
ALTER DATABASE [PartitionThis] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [PartitionThis];
END
GO
CREATE DATABASE [PartitionThis]
GO
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 96MB );
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis_log', SIZE = 128MB , FILEGROWTH = 96MB );
GO
USE PartitionThis;
GO
--*******************************
--2 CREATE HELPER OBJECTS
--Why do we need these?
--Do they HAVE to be in the database with the partitioned objects?
--*******************************
--Create a schema for "partition helper" objects
CREATE SCHEMA [ph] AUTHORIZATION dbo;
GO
--Create a view to see partition information by filegroup
CREATE VIEW ph.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
--Create a view to see partition information by object
CREATE VIEW ph.ObjectDetail
AS
SELECT SCHEMA_NAME(so.schema_id) AS schema_name ,
OBJECT_NAME(p.object_id) AS object_name ,
p.partition_number ,
p.data_compression_desc ,
dbps.row_count ,
dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,
si.index_id ,
CASE WHEN si.index_id = 0 THEN '(heap!)'
ELSE si.name
END AS index_name ,
si.is_unique ,
si.data_space_id ,
mappedto.name AS mapped_to_name ,
mappedto.type_desc AS mapped_to_type_desc ,
partitionds.name AS partition_filegroup ,
pf.name AS pf_name ,
pf.type_desc AS pf_type_desc ,
pf.fanout AS pf_fanout ,
pf.boundary_value_on_right ,
ps.name AS partition_scheme_name ,
rv.value AS range_value
FROM sys.partitions p
JOIN sys.objects so
ON p.object_id = so.object_id
AND so.is_ms_shipped = 0
LEFT JOIN sys.dm_db_partition_stats AS dbps
ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
JOIN sys.indexes si
ON p.object_id = si.object_id
AND p.index_id = si.index_id
LEFT JOIN sys.data_spaces mappedto
ON si.data_space_id = mappedto.data_space_id
LEFT JOIN sys.destination_data_spaces dds
ON si.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds
ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.partition_schemes AS ps
ON dds.partition_scheme_id = ps.data_space_id
LEFT 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
GO
;
WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1),
Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B),
Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B),
Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B),
Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B),
Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B),
tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 )
SELECT N
INTO ph.tally
FROM tally
WHERE N <= 100000;
GO
DECLARE @StartDay DATE=
DATEADD(dd,-63,CAST(SYSDATETIME() AS DATE));
CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))
AS RANGE RIGHT FOR VALUES
(@StartDay,DATEADD(dd,30,@StartDay), DATEADD(dd,61,@StartDay),
DATEADD(dd,92,@StartDay), DATEADD(dd,120,@StartDay) );
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3
GO
DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);
SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)
FROM sys.database_files WHERE name='PartitionThis';
WHILE @i <= 3
BEGIN
SET @sql=N'ALTER DATABASE PartitionThis ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',
filename=''' + @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',
size=128MB, filegrowth=96MB) TO FILEGROUP DailyFG'+CAST(@i AS NCHAR(1))
--show the command we're running
RAISERROR (@sql,0,0)
--run it
EXEC sp_executesql @sql;
SET @i+=1;
END
GO
CREATE PARTITION SCHEME DailyPS
AS PARTITION DailyPF
TO (DailyFG1,DailyFG2,DailyFG2,DailyFG2,DailyFG3,DailyFG3);
if OBJECT_ID('OrdersDaily','U') is null
CREATE TABLE OrdersDaily (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on DailyPS(OrderDate)
GO
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-63,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Robot' WHEN t.N % 4 = 0 THEN 'Badger' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 1000;
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-59,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Robot' WHEN t.N % 4 = 0 THEN 'Badger' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 1000;
--Two days ago = 2000 rows
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-30,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 3 = 0 THEN 'Flying Monkey' WHEN t.N % 4 = 0 THEN 'Junebug' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 2000;
--Yesterday= 3000 rows
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-01,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
CASE WHEN t.N % 2 = 0 THEN 'Turtle' WHEN t.N % 5 = 0 THEN 'Eraser' ELSE 'Pen' END AS OrderName
FROM ph.tally AS t
WHERE N < = 3000;
--Switch Out tables
CREATE TABLE OrdersDailySwitchOut_p1 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG2];
GO
CREATE TABLE OrdersDailySwitchOut_p2 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG2];
GO
CREATE TABLE OrdersDailySwitchOut_p3 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL
) on [DailyFG2];
GO
--Switch out
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 2 TO OrdersDailySwitchOut_p1;
GO
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 3 TO OrdersDailySwitchOut_p2;
GO
RAISERROR ('Switching out.',0,0)
ALTER TABLE OrdersDaily
SWITCH PARTITION 4 TO OrdersDailySwitchOut_p3;
GO
SELECT *
FROM ph.ObjectDetail
WHERE object_name IN ('OrdersDaily','OrdersDailySwitchOut_p1','OrdersDailySwitchOut_p2','OrdersDailySwitchOut_p3')
ORDER BY object_name DESC, partition_number;
GO
--Merge
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-12-04 00:00:00.000' )
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2013-11-04 00:00:00.000' )
ALTER PARTITION FUNCTION DailyPF ()
MERGE RANGE ( '2014-01-04 00:00:00.000' )
SELECT *
FROM ph.FileGroupDetail;
GO
--Drop Staging table
DROP TABLE OrdersDailySwitchOut_p1;
DROP TABLE OrdersDailySwitchOut_p2;
DROP TABLE OrdersDailySwitchOut_p3;
GO
--Remove File succesfully.
USE partitionthis
ALTER DATABASE partitionthis REMOVE FILE DailyF2
Go
--PROBLEM-- filegroup is not empty
ALTER DATABASE partitionthis REMOVE FILEGROUP DailyFG2
SELECT *
FROM sys.filegroups
January 6, 2014 at 2:58 pm
Hi Keith,
Thank you so much for looking into my question. I have watched Kendra's video and tried out your script. It works perfect.
In my case, the partition was already build by someone. I will need to help them to drop the old file group. for example DailyFG1.
How can I alter parttion scheme to add an extra partition?
After I run merge boundary, I still see DailyFG1 is still used. Partition Scheme is as shown below.
CREATE PARTITION SCHEME [DailyPS] AS PARTITION [DailyPF] TO ([DailyFG1], [DailyFG2], [DailyFG2], [DailyFG2])
GO
January 6, 2014 at 11:29 pm
ayemya (1/6/2014)
Hi Keith,Thank you so much for looking into my question. I have watched Kendra's video and tried out your script. It works perfect.
In my case, the partition was already build by someone. I will need to help them to drop the old file group. for example DailyFG1.
How can I alter parttion scheme to add an extra partition?
After I run merge boundary, I still see DailyFG1 is still used. Partition Scheme is as shown below.
CREATE PARTITION SCHEME [DailyPS] AS PARTITION [DailyPF] TO ([DailyFG1], [DailyFG2], [DailyFG2], [DailyFG2])
GO
Did you check the DailyFG1 File Group to see if it was using another file?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2014 at 8:42 am
ayemya (1/6/2014)
Hi Keith,Thank you so much for looking into my question. I have watched Kendra's video and tried out your script. It works perfect.
In my case, the partition was already build by someone. I will need to help them to drop the old file group. for example DailyFG1.
How can I alter parttion scheme to add an extra partition?
After I run merge boundary, I still see DailyFG1 is still used. Partition Scheme is as shown below.
CREATE PARTITION SCHEME [DailyPS] AS PARTITION [DailyPF] TO ([DailyFG1], [DailyFG2], [DailyFG2], [DailyFG2])
GO
You are welcome, glad to help.
Why do you need to drop the old filegroup? At this point you have moved the data and dropped the tables that you switched out to so the filegroup is empty and this gives you what you need (at least half of what you need) with empty partitions at either end. Why not keep DailyFG1 as your far left empty partition (don't ever add data or objects to it) and then next time it will be that much easier for you to do this operation.
DailyFG1 is still used because it is the far left boundary point (range_value = null).
January 7, 2014 at 2:17 pm
Hi Keith,
yes. I will keep DailyFG1 for a lowest boundary. Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply