December 13, 2010 at 11:24 am
I can't make my 12-month sliding scenario work with my Range Right partition function, and haven't been able to find code anywhere that actually does it. I also posted a question about the issues I was having last week, with the result of no solutions suggested.
So I have to wonder: Has anyone tried this and gotten it to work???
thanks,
Amy
December 13, 2010 at 12:28 pm
Not enough information. Post your Partition Scheme and Partition Function. Are you returning only the month in the Partition Function?
December 13, 2010 at 12:36 pm
It will help if you can share your partition specs along with script that you are using.
_____________
Vikas S. Rajput
December 13, 2010 at 12:46 pm
thanks for your interest. See my notes down below where it blows up...
ALTER DATABASE edw ADD FILEGROUP [201001]
ALTER DATABASE edw ADD FILEGROUP [201002]
ALTER DATABASE edw ADD FILEGROUP [201003]
ALTER DATABASE edw ADD FILEGROUP [201004]
ALTER DATABASE edw ADD FILEGROUP [201005]
ALTER DATABASE edw ADD FILEGROUP [201006]
ALTER DATABASE edw ADD FILEGROUP [201007]
ALTER DATABASE edw ADD FILEGROUP [201008]
ALTER DATABASE edw ADD FILEGROUP [201009]
ALTER DATABASE edw ADD FILEGROUP [201010]
ALTER DATABASE edw ADD FILEGROUP [201011]
ALTER DATABASE edw ADD FILEGROUP [201012]
-- Add files to the filegroups
/*
ALTER DATABASE edw
ADD FILE
(NAME = '2009',
FILENAME = 'G:\mssql\data\2009.ndf',
SIZE = 100000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [2009]
*/
ALTER DATABASE edw
ADD FILE
(NAME = '201001',
FILENAME = 'H:\mssql\data\201001.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201001]
ALTER DATABASE edw
ADD FILE
(NAME = '201002',
FILENAME = 'H:\mssql\data\201002.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201002]
ALTER DATABASE edw
ADD FILE
(NAME = '201003',
FILENAME = 'H:\mssql\data\201003.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201003]
ALTER DATABASE edw
ADD FILE
(NAME = '201004',
FILENAME = 'H:\mssql\data\201004.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201004]
ALTER DATABASE edw
ADD FILE
(NAME = '201005',
FILENAME = 'H:\mssql\data\201005.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201005]
ALTER DATABASE edw
ADD FILE
(NAME = '201006',
FILENAME = 'H:\mssql\data\201006.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201006]
ALTER DATABASE edw
ADD FILE
(NAME = '201007',
FILENAME = 'H:\mssql\data\201007.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201007]
ALTER DATABASE edw
ADD FILE
(NAME = '201008',
FILENAME = 'H:\mssql\data\201008.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201008]
ALTER DATABASE edw
ADD FILE
(NAME = '201009',
FILENAME = 'H:\mssql\data\201009.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201009]
ALTER DATABASE edw
ADD FILE
(NAME = '201010',
FILENAME = 'H:\mssql\data\201010.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201010]
ALTER DATABASE edw
ADD FILE
(NAME = '201011',
FILENAME = 'H:\mssql\data\201011.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201011]
ALTER DATABASE edw
ADD FILE
(NAME = '201012',
FILENAME = 'H:\mssql\data\201012.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201012]
CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES ('20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')
-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO ([201001],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [PRIMARY])
CREATE TABLE [dbo].[fill_fact]
(
time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null
)
ON dispense_date_pscheme (dispense_date)
--And here is my sliding window manipulation script:
ALTER DATABASE edw ADD FILEGROUP [201101]
ALTER DATABASE edw ADD FILE (NAME = '201101', FILENAME = 'H:\mssql\data\201101.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201101]
CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
--greater than 20110101
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101')
) ON [201101]
CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null
)
ON [201001]
ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201101]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')
/*this next step is where it chokes, because partition 12 was not created on 201101, but on Primary.
partition 13 was created on Filegroup 201101. This is where it confuses me, because when I split the range (added the 20110201 boundary), I think it should have split Primary (the empty end partition 13), and inserted the new partition on 201101 in as partition 12. Is this not correct? */
ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
ALTER DATABASE edw REMOVE FILE [201001]
ALTER DATABASE edw REMOVE FILEGROUP [201001]
December 14, 2010 at 7:13 am
FYI, for anyone interested, I did get it to work. I had to deviate from the way that Kimberly Tripp had said to do it, but at least it works. Instead of the final partition being created on Primary, it had to be created on a real filegroup that is eventually going to be utilized. After the final partition is split, the [now] last partition is the one on the filegroup that was indicated as "next used". Here is the new successful code for the Range Right 12-month sliding window update (without repeating all the creation scripts already presented above -- just showing additional or changed code):
Also Note: This code is dynamic in the sense that it was automatically generated from within a script. I just did a whole lot of print statements for the dynamic sql that was being created to get the resulting script for testing...
ALTER DATABASE edw ADD FILEGROUP [201101]
ALTER DATABASE edw
ADD FILE
(NAME = '201101',
FILENAME = 'H:\mssql\data\201101.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201101]
CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES (/*'2009',*/ '20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')
-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO (/*[2009],*/[201001],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [201101])
ALTER DATABASE edw ADD FILEGROUP [201102]
ALTER DATABASE edw ADD FILE (NAME = '201102', FILENAME = 'H:\mssql\data\201102.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201102]
CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101'
AND [dispense_date] < '20110201')
) ON [201101]
CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null)
ON [201001]
ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201102]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')
ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
--the following doesn't work yet -- there seems to be stuff sticking around in this File/FG...
ALTER DATABASE edw REMOVE FILE [201001]
ALTER DATABASE edw REMOVE FILEGROUP [201001]
December 14, 2010 at 8:40 am
wait a minute -- having similar issue on the other side of the range. Maybe Primary needed to be in the first partition... checking that out now...
December 14, 2010 at 10:09 am
OK, figured out where the Primary filegroup belongs on a Range Right partitioning function, and why...
here is the new code, for anyone trying to do this:
CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES ('20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')
-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO ([PRIMARY],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [201101])
ALTER DATABASE edw ADD FILEGROUP [201102]
ALTER DATABASE edw ADD FILE (NAME = '201102', FILENAME = 'H:\mssql\data\201102.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201102]
CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101'
AND [dispense_date] < '20110201')
) ON [201101]
CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null)
ON [PRIMARY]
ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201102]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')
ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
ALTER DATABASE edw REMOVE FILE [201002]
ALTER DATABASE edw REMOVE FILEGROUP [201002]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply