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.