8

I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.

Can anyone please advise, what's the best way to create new number of partitions.

I am doing the following step (Yet not did)

  1. From SSMS, get the properties of Database and move to Files tab.
  2. Click Add button on the bottom right to add a new line.
  3. Put a new file name (e.g. FN_DW_Archieve_2013_02)
  4. Put a new Logical Name (e.g. LN_DW_Archieve_2013_02)
  5. Create a new Filegroup by selecting in the dropdown under filegroup cell. (e.g. FG_DW_Archieve_2013_02)
  6. Hit Ok

This will create a new file group in my database.

Now the biggest problem which make me worry is to modify the Partition Function and Partition Scheme. Which expert advise, can I drop and recreate them again, does it affect my existing data. Or can I alter them with the new partition in it.

Thanks in advance.

| improve this question | |
9

It sounds like what you want to do is to create a new boundary for your existing partitioning implementation, and this can be one through ALTER PARTITION FUNCTION ... SPLIT RANGE ....

You will also need to run prior to this an ALTER PARTITION SCHEME ... NEXT USED ... to indicate the next filegroup that will hold the new partition (there are a few caveats to this, as quoted below). Here is a quote from BOL regarding this operation:

A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition.

| improve this answer | |
  • So these are my partition functions and partition scheme CREATE PARTITION FUNCTION [ArchivePartitionFunction](smalldatetime) AS RANGE FOR VALUES (N'2012-12-30T23:59:00.000', N'2013-01-31T23:59:00.000') GO CREATE PARTITION SCHEME [ArchivePartitionScheme] AS PARTITION [ArchivePartitionFunction] TO ([Archive_201212], [Archive_201301], [PRIMARY]) GO – Muhammad Sharjeel Ahsan Nov 20 '12 at 23:26
  • When I tries to modify scheme, its fine ALTER PARTITION SCHEME ArchivePartitionScheme NEXT USED Archive_201302 GO But altering function gives error ALTER PARTITION FUNCTION [ArchivePartitionFunction]() MERGE RANGE('2013-02-28T23:59:00.000') GO The specified partition range value could not be found. – Muhammad Sharjeel Ahsan Nov 20 '12 at 23:31
  • First off, why are you doing a MERGE? I thought you wanted additional partitions. Also, the boundary must exist to do a MERGE. – Thomas Stringer Nov 21 '12 at 0:25
  • Thanks got it, following code works great. ALTER PARTITION SCHEME ArchivePartitionScheme NEXT USED Archive_201302 GO ALTER PARTITION FUNCTION [ArchivePartitionFunction]() SPLIT RANGE('2013-02-28T23:59:00.000') GO – Muhammad Sharjeel Ahsan Nov 21 '12 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.