Sql-server – Unable to create / Move data to New Partitions from the existing partition SQL Server 2014

partitioningsql serversql server 2014table

We have an SQL Server 2014 Enterprise Edition and a database around 6 TB in size.

Just to give quick background of infra as Server is in Azure and having premium disk of 9 TB for datafiles.

Have a few large table that is already partitioned by an identifier (MonthlyDate integer Eg 01012016,… 31122016 ). We have monthly partition starting from 2014 to 2016 (01012014 , 01022014……till 31122016).

Now we are trying to create partition for 2017 and 2018 month-wise.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

Alter partition Scheme [PartPScheme_BIGTAB] Next Used [PartFileGrp_201701]

Alter partition Function [PartPFN_BIGTAB] () split range(20170131)

I have tried to run the abrove script multiple time , But it have taken more then 7 hours just only 1 gb of the data has been moved to above partition and finally we have to roll back the script.

I'm looking for someone to point me in the right direction as to best practice for this sort of thing. Not really sure how to approach this.

What could be the issue?

The current partition function is:

CREATE PARTITION FUNCTION [PartPFN_BIGTAB](int) AS RANGE LEFT FOR VALUES (
      20141031
    , 20141131
    , 20141231
    , 20150131
    , 20150231
    , 20150331
    , 20150431
    , 20150531
    , 20150631
    , 20150731
    , 20150831
    , 20150931
    , 20151031
    , 20151131
    , 20151231
    , 20160131
    , 20160231
    , 20160331
    , 20160431
    , 20160531
    , 20160631
    , 20160731
    , 20160831
    , 20160931
    , 20161031
    , 20161131
    , 20161231
    );

Best Answer

If your intention is to partition this data by year/month, I think your current partitioning column (which is an integer and not a 'true' date data type and not even in yyyymmdd format even as an integer) is all wrong for the job. I'm also assuming you don't have another column in the table that IS the MonthlyDate in a true Date data type.

In my opinion (and assuming you have extra disk space), you should:

  • Create a new partition function that uses a DATE data type. It is recommended that this should be RANGE RIGHT (see Dan Guzman's link at the bottom of this answer for explanations). Make sure you create it with ranges well below your minimum date as well as ranges well above your maximum date. Also, as time goes by, make sure you are splitting empty partitions well ahead of any data already being populated in them.
  • Create a new partition scheme that references the new partition function.
  • Create a new 'replacement' table (that references the newly created partition scheme) by scripting a create of the current table, but add a computed/persisted DATE column (ex:PartitionedColumn DATE) that is derived/converted from your integer MonthlyDate column.
  • Set up an insert statement, inserting records from the existing table into the new table, say a few thousand or a hundred thousand in a batch, within a transaction. When the transaction completes perform a log backup (if you're not in SIMPLE recovery). That'll keep you transaction log from running out of space. Then repeat the batch... keep doing that until all records are written to the new table with the persisted computed column.
  • Then change the old table name, change the new table name to that of the old table

Keep in mind that all unique indexes that participate in a partitioning scenario, MUST have the partitioning column defined as part of the base index definition and not simply an included column.

Also, to enable partition switching (often used in a "sliding window"), all indexes on the table must be aligned. BOL has good information about this. Look for Special Guidelines for Partitioned Indexes.

Lastly, check out Dan Guzman's excellent post on Sql Server partitioning.

Related Question