August 5, 2009 at 2:51 pm
I have a data partition in which a previous DBA failed to set it up correctly according to the guidelines of our naming convention. The problem is that the left most range of the scheme (the one that should contain no data) is set to 200803 which is the same file as the first real range (see code below) and further details after code.
CREATE PARTITION FUNCTION [MonthlyPartition_F01](datetime) AS RANGE RIGHT FOR VALUES (
N'2008-03-01T00:00:00.000'
, N'2008-04-01T00:00:00.000'
, N'2008-05-01T00:00:00.000'
, N'2008-06-01T00:00:00.000'
, N'2008-07-01T00:00:00.000'
, N'2008-08-01T00:00:00.000'
, N'2008-09-01T00:00:00.000'
, N'2008-10-01T00:00:00.000'
, N'2008-11-01T00:00:00.000'
, N'2008-12-01T00:00:00.000'
, N'2009-01-01T00:00:00.000'
, N'2009-02-01T00:00:00.000'
, N'2009-03-06T00:00:00.000'
, N'2009-04-01T00:00:00.000'
, N'2009-05-01T00:00:00.000'
, N'2009-06-01T00:00:00.000'
, N'2009-07-01T00:00:00.000'
)
GO
CREATE PARTITION SCHEME [MonthlyPartition_SC01] AS PARTITION [MonthlyPartition_F01] TO (
[TRANDTA_200803]
, [TRANDTA_200803]
, [TRANDTA_200804]
, [TRANDTA_200805]
, [TRANDTA_200806]
, [TRANDTA_200807]
, [TRANDTA_200808]
, [TRANDTA_200809]
, [TRANDTA_200810]
, [TRANDTA_200811]
, [TRANDTA_200812]
, [TRANDTA_200901]
, [TRANDTA_200902]
, [TRANDTA_200903]
, [TRANDTA_200904]
, [TRANDTA_200905]
, [TRANDTA_200906]
, [TRANDTA_200907]
)
GO
We would normally have the scheme setup as follows:
CREATE PARTITION SCHEME [MonthlyPartition_SC01] AS PARTITION [MonthlyPartition_F01] TO (
[TRANDTA01]
, [TRANDTA_200803]
, [TRANDTA_200804]
, [TRANDTA_200805]
, [TRANDTA_200806]
, [TRANDTA_200807]
, [TRANDTA_200808]
, [TRANDTA_200809]
, [TRANDTA_200810]
, [TRANDTA_200811]
, [TRANDTA_200812]
, [TRANDTA_200901]
, [TRANDTA_200902]
, [TRANDTA_200903]
, [TRANDTA_200904]
, [TRANDTA_200905]
, [TRANDTA_200906]
, [TRANDTA_200907]
)
GO
Where TRANDTA01 is a placeholder. Is there a way to alter the scheme to replace this without merging data, or removing the partition and re-creating it? This partition holds tens of millions of records and I'd prefer to not have to make data move to do this.
Thanks in advance.
August 6, 2009 at 9:00 am
bump...anyone have any suggestions?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply