January 17, 2014 at 7:10 am
I have a table with multiple partitions all assigned to the same file group. Is there a way i can modify the partition scheme for this table to assign the existing empty partitions to different FGs, without having to drop and recreate schemes and functions from scratch. the table is super huge and i am not really looking at more than an hour downtime to perform any kind of operation.
Any help is appreciated!
January 17, 2014 at 7:42 am
You don't have to recreate the functions/schemes from scratch to change empty partitions. You can merge all of the empty partitions and use split to reassign them to the different filegroup. This can be done online in most cases with a short lived schema modification lock.
January 17, 2014 at 9:07 am
MysteryJimbo (1/17/2014)
This can be done online in most cases with a short lived schema modification lock.
If the split involves moving rows from one partition to another, it's not going to be a quick operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2014 at 9:13 am
As author noted, all partitions assigned to the same filegroup, so it won't physically move the rows, it will just re-assign partitions within.
January 17, 2014 at 9:18 am
GilaMonster (1/17/2014)
MysteryJimbo (1/17/2014)
This can be done online in most cases with a short lived schema modification lock.If the split involves moving rows from one partition to another, it's not going to be a quick operation.
He said they were empty
raj_harsh (1/17/2014)
to assign the existing empty partitions to different FGs
So as long as the partitions aren't merged with partitions that have rows it can be done.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply