Post reply

Add partition to existing table

  • December 28, 2014 at 10:20 pm

    #314164

    I have a partitioned table on Date column which contains billions of rows. The last partition for the table was 2014-01-01 (has own FG) and all the data (which is also in millions) after this date comes to PRIMARY filegroup. Now to add more partitions to this table, I am trying NEXT USED & SPLIT but it is taking more than 24 hours and consumes all the log disk space.

    Can anyone suggest me to add partitions in effective manner.

    Thanks in advance.

  • December 29, 2014 at 1:26 am

    #1766669

    See this http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 29, 2014 at 1:47 am

    #1766674

    You use SPLIT to create new partitions. If it's taking time, it implies that there's data in the table that has to be moved to the new partition. This is generally not a recommended situation, you should split partitions so that the newly created partition is empty. If data has to move as a result of the new partition, it will take a lot of time and use a lot of log space.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 29, 2014 at 1:50 am

    #1766676

    Thanks Bhuvnesh.

    But here my table (lets say T1) is already partitioned till 2014-01-01 but the data (~600M) which comes after 2014-01-01 goes to PRIMARY filegroup.

    Now to add more partitions to the table T1, I created a non-partitioned table on primary fg (where the data after 2014-01-01 goes) and then switch the partition. And then added the desired partition along with specific filegroup to the table.

    The main concern is coming while moving back 600M rows (from non-partitioned table) to the partitioned table T1 as non-partitioned table is in primary fg but each partition in T1 has its own FG.

  • December 29, 2014 at 1:53 am

    #1766678

    Tarun Jaggi (12/29/2014)

    The main concern is coming while moving back 600M rows (from non-partitioned table) to the partitioned table T1 as non-partitioned table is in primary fg but each partition in T1 has its own FG.

    How are you moving data ? post your query here .

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 29, 2014 at 1:59 am

    #1766682

    Unfortunately, if you are splitting the partition and existing data belongs in the new partition, you're just going to have to accept that it will take time and you'll need to ensure that there's enough log space for SQL to move the data.

    Alternately, you could create a new table with the partitions as you want them and copy the data over. It'll also take time, but you can do it in batches to reduce the log impact.

    Either way, the data will have to move, there's no way to create partitions and not have the data that qualifies for the new partition be automatically moved.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 29, 2014 at 2:01 am

    #1766683

    I tried SWITCH partition back from non-partitioned to partitioned table but that fails because of different FG.

    I wrote a script to pull the data of date basis and populate to partitioned table T1 but this will take 6 hours to complete.

    Is there any efficient way to move data?

  • December 29, 2014 at 2:02 am

    #1766685

    Thanks Gila.

    Will try the second option.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply