Sql-server – strange situation with very large partitioning table

partitioningsql server

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?

Best Answer

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.

Related Solutions

Mysql – Large accounting table, best way to partitioning by two dates

Since stop_datetime will not be known at insert time the partition must be done by start_datetime.

I think partitioning by day (date_format(start_datetime, '%Y-%m-%d')) would be adequate since 1 billion rows divided by (3 years * 365 days) ≃ 900,000 rows per partition

Updating indexes is expensive and it happens at inserts, updates and deletes. Keep the indexes to a minimum. If the main queries will be those selects then I would keep the primary key like the following and trash all the other keys:

PRIMARY KEY (`user`,`start_datetime`,`stop_datetime`,`mac`,`ip`)

Sql-server – How to partition a very large table with limited db space

You'll need to create a new table with the same schema, but as a partitioned object. Optionally you can compress the table to save even more space. As you are only putting on average one row per page I'm not sure how much space savings you will see. I'd recommend putting a few thousand rows into the new table then compressing to see if the space savings is worth the CPU overhead.

As for how to move this much data without eating up all your drive space and without bloating your transaction log, that'll need to be done in a loop moving small amounts of data per run. You'll want to do some data analysis to see how large of a window you can process, but I'll assume that based on the data volumes you need to move the rows one minute at a time.

DECLARE @processFrom as datetime
SELECT @processFrom = min(YourDateColumn)
FROM YourTable

DECLARE TABLE @Rows (...)

WHILE EXISTS (SELECT * FROM YourTable)
BEGIN
     DELETE TOP (10000) FROM YourTable
     OUTPUT DELETED.* INTO @Rows
     WHERE YourDateColumn = @processFrom


     INSERT INTO NewTable
     (...)
     SELECT ...
     FROM @Rows

     DELETE FROM @Rows

     IF @@ROWCOUNT = 0
              SET @processFrom = dateadd(dd, 1, @processFrom)
END

When everything is done and you've verified that all the data is in the new table drop the old table and rename the new table so that it has the old tables name. This way nothing breaks. You'll want to script out the permissions on the old table (if there are any) so that you can apply them to the new table.

If there are tables with foreign keys to this table you'll need to drop them before this will work.

Best Answer

Related Solutions

Mysql – Large accounting table, best way to partitioning by two dates

Sql-server – How to partition a very large table with limited db space

Related Question