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 );