November 24, 2016 at 6:28 am
Hi Guys,
As I am not much familar with partition concept of SQL Server, I need your help to add a new partition to a partitioned table, my partition function is RANGE LEFT for values. the un allocated partition hold data from 2013-12 to till date (2016-12), hence the decemeber will be the year end we are planning to add the new partition for 2017, need to split the current Left most partition to two, ie. 2013-12 to 2016-12 will be in one partition, the rest of the new data from 2017-01 onwards will remain in the current partition
I tried this with ALTER PARTITION FUNCTION.. SPLIT RANGE option, but it is consuming more time (it took more than 2 hours in developement server itself), then I tried SWITCH option to move data OUT from a partition, it went well, but while SWTICH IN the data, the file group got changed and it is not happening. thus I need your valued suggestion to achieve this in a faster way.
Thanks in advance,
Prabhu.
here is the PARTITION FUNCTION AND SCHEME for your reference.
CREATE PARTITION FUNCTION [pf_MyPartitionFunction](int) AS RANGE LEFT FOR VALUES
(200012, 200112, 200212, 200312, 200412, 200512, 200612, 200712,
200812, 200912, 201012, 201112, 201212)
GO
CREATE PARTITION SCHEME [ps_MyPartitionScheme]
AS PARTITION [pf_MyPartitionFunction] TO
([MyPartitionValue200012fg], [MyPartitionValue200112fg], [MyPartitionValue200212fg],
[MyPartitionValue200312fg], [MyPartitionValue200412fg], [MyPartitionValue200512fg],
[MyPartitionValue200612fg], [MyPartitionValue200712fg], [MyPartitionValue200812fg],
[MyPartitionValue200912fg], [MyPartitionValue201012fg], [MyPartitionValue201112fg],
[MyPartitionValue201212fg], [MyPartitionValueCurrentfg])
GO
November 24, 2016 at 8:57 am
I don't understand this statement:
but while SWTICH IN the data, the file group got changed and it is not happening.
What error did it throw? Or is this just the part that takes a long time? Did you use sp_whoisactive to see what work was being done? Moving substantial amounts of data between filegroups can indeed take a long time on many systems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 24, 2016 at 9:19 am
Hi Kevin,
Initially I created the Staging table in a file group ([MyPartitionValueCurrentfg]) where the data which I want to move is residing in the partitioned table. so I was able to SWITCH-OUT the data successfully, now I am altering the SCHEME and the FUNCTION of the partition to create a new one, and named it as [MyPartitionValue201612fg], once this step is executed the data boundary values for this partition also swapped (ie. from 2013-12 to 2016-12 data is the boundary for this partition in my case).
As per the above scenario, the staging table is in MyPartitionValueCurrentfg filegroup and the Partition to which I want to SWITCH-IN is in the MyPartitionValue201612fg filegroup. so the SWITCH ended with error.
kindly advice how can I achieve this without a bottle neck.
Thanks,
Prabhu
November 24, 2016 at 10:05 am
Did you read the requirements for doing a SWITCH before you decided to use paritioning?? It is quite clear that what you are trying to do is not allowed:
https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx
The fifth of the five requirments is this:
Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply