I have a very strange situation here...

On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last partition has a boundary 201205110000.

All Data after 201205120000 is before the last boundary 201205110000, now i thought I can make the new boundary ranges, but the fi case is, If I make the next boundary 201205120000 sql server copy all data from 201205110000 to the boundary 201205120000, this makes for me sense, but I can’t do this because my transaction log becomes full. also the specific file, in this case file_20120512, is growing.

If thought I can create the last boundary 201206040000, then 201206030000, but if I do this SQL Servers tran log runs full but the specific data file file_20120604 does not growing but the tran log …

What can I do?


So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation):

  • drop all unaligned indexes
  • create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition
  • switch the last partition into the staging table. now the data has moved into the staging table, the last partition of the real table is empty
  • split the partitioning function as needed, since the last partition is empty the operations will be instantaneous. Remember to leave an empty last partition
  • create a temporary partitioning scheme for the staging table aligned with the partitions in the tail of the real table
  • rebuild the staging table on the temporary partition scheme created above. Now the staging table is aligned with the empty partitions in the tail of the real table. If the rebuild has log space issues use an online rebuild and take frequent log backups. consider also enabling bulk_logged mode.
  • switch each partition one by one from the staging table into the real table.
  • add back any unaligned index you need (you shouldn't have unaligned indexes to start with if you ask me...)
  • drop the staging table and temp partitioning function/scheme
  • start maintaining the partitioning so you don't have to repeat this next time.
  • 1
    @BenediktSchackenberg - Remember to mark this as answered if that resolved your question. – Martin Smith Jun 15 '12 at 7:31

Your Answer

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