Question: I am thinking that without filling up the transaction log, I can apply an index to the table.
Extension: I have a table which is 800 GB. A few months ago I went to apply an index to it and the transaction log filled (for obvious reasons - I was dumb to try it too) . Instead of making the table again, I think implement the index and then copy the record.
Now we are going to set up a division in this table. I am thinking that if I remove the package indicator and apply the new clustered index to the partition layout, if it still will complete the transaction log (assuming there are 10 million rows per partition)? Or will the toggle not be filled because the indexes will also be split which will allow SQL Server to be able to complete the index faster and be able to checkpoint?
Do anyone have any ideas? Otherwise, I can re-create the table again and implement the partitioning material on it and I can fill it again, but it is obvious that it is much more involved.
Thank you!
The implementation of the partitioning scheme will result in a big log, yes.
Firstly, I advise you to tell that if possible, one can get one. Create the offline index is subject to minimum logging provided the database is in bulk log mode.
If it is not possible (like full recovery is required) I will try to do the partition table online. Online operations work in small batches and work often. Unless you continuously backup the logs, it will not grow bigger.
Comments
Post a Comment