Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 10 '13 at 15:43

This question came from our site for professional and enthusiast programmers.

    
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 '13 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 '13 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 '13 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 '13 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 '13 at 22:16

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '13 at 20:10
    
OK, that's easy enough to deal with. I've changed the sample code to handle that. –  mrdenny Jun 11 '13 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 '13 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 '13 at 9:52
    
Where these rules come from? Is it limitation of partitioning or using `OUTPUT clause? –  Stoleg Jun 13 '13 at 10:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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