3

I have a very big table. I want to partition it, but I cannot.

Database size: 1TB, Free space 200GB

Table:

  • Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
  • Possible partitioning: a day per partition
  • Number of rows per day/parition: 2 million

In order to partition it, I need to create a clustered index. But creating parition requires free space of the same size as the table, and I do not have extra 600GB.

Are there any options how I partition this table?

EDIT 1:

I have tried copying data into separate table.

However when I try to DELETE (or INSERT) 1 day of data into another table, I get an error, that tranasaction log is full and my transaction is being rolled back. My transaction log is approx 20 GB and I cannot make it any bigger.

14
  • Is it possible to create a second table and then do it in stages? Load a day of data into the new table and then delete the loaded data from the original table?
    – user1948904
    Jun 10, 2013 at 15:18
  • @user1948904, It's a good idea. However when I try to delete (or insert) 1 day of data into another table, I get an error, that tranasaction log is full and my transaction is being rolled back. My transaction log is approx 20 GB and I cannot make it bigger. May be you can think of something else?
    – Stoleg
    Jun 10, 2013 at 15:34
  • 1. You can partition a heap. A Clustered index isn't needed. 2. You'll still need the space as you have to do a rebuild of the heap and write it to new pages which will require 2x the space. The separate table approach will be the only option that works. You'll need to batch the inserts to save log space.
    – mrdenny
    Jun 10, 2013 at 16:07
  • @mrdenny, I'll have a look into partitioning a heap, but how can I consistenlty copy into another table when I cannot 1 full day of data and I have no unique key either?
    – Stoleg
    Jun 10, 2013 at 19:30
  • 1
    If there's an inserted date field you can use that by minute, or even by second. It'll take quite a while to move that much data. It'll probably be a LOT faster and easier to simply purchase more storage for the system.
    – mrdenny
    Jun 10, 2013 at 22:16

1 Answer 1

5

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.

7
  • Thanks a lot! This is very close. But my data has no minutes. It is just a date as '2013-06-10 00:00:00.000'.
    – Stoleg
    Jun 11, 2013 at 20:10
  • OK, that's easy enough to deal with. I've changed the sample code to handle that.
    – mrdenny
    Jun 11, 2013 at 20:12
  • OUTPUT DELTED does the trick! But why do you need the variable? Can you just OUTPUT into new table?
    – Stoleg
    Jun 12, 2013 at 7:53
  • It can be a table but there are specific rules about the target table. Specifically: It can't have enabled triggers defined on it. It can't participate on either side of a FOREIGN KEY constraint. It can't have CHECK constraints or enabled rules.
    – mrdenny
    Jun 13, 2013 at 9:52
  • Where these rules come from? Is it limitation of partitioning or using `OUTPUT clause?
    – Stoleg
    Jun 13, 2013 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.