How to add new filegroup to Existing partition scheme

• PRAMANA.DBA

  SSCertifiable

  Points: 5507

  More actions

  July 10, 2014 at 1:12 am

  #288513

  Hi,

  How to add some more ranges to existing partition schema and function?

  Already My table partitioned on date ranges,

  6 partitions , each partition contains 6 moths data, so total data is 3 years.

  i.e. 1 partition data- from jan2012 to Jun2012

  2 partition data- from july2012 to dec2012

  3 partition data- from jan2013 to Jun2013

  4 partition data- from july2013 to dec2013

  5 partition data- from jan2014 to Jun2014

  6 partition data- from july2014 to dec2014

  After Jan2015 data will go to Primary file group(Default)

  Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.

  File group and ndf file adding is OK, But

  how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?

  partitioned table size is 200 GB.

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  July 10, 2014 at 5:25 am

  #1728328

  You need to use ALTER PARTITION SCHEME to put next set of data & ALTER PARTITION FUNCTION with SPLIT RANGE command to do the job.

  Refer:

  ALTER PARTITION SCHEME - http://technet.microsoft.com/en-us/library/ms190347(v=sql.110).aspx

  ALTER PARTITION FUNCTION - http://technet.microsoft.com/en-us/library/ms186307(v=sql.110).aspx

  Sample code:

  ALTER PARTITION SCHEME partScheme NEXT USED [Primary]

  ALTER PARTITION FUNCTION fnPartitionYear() SPLIT RANGE ('1/1/2013')

  ALTER PARTITION FUNCTION fnPartitionYear() SPLIT RANGE ('1/1/2014')

  Thanks