3

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!.

I have a 27GB database with around 30GB log. The table that is beeing deleted from is around 15GB in size, with 20 million records in it. The table has 20 columns only with bigint and int data types.

The delete operation is deleting 18 million duplicate records from the table in question. I have switched selected for the delete to make sure the record count matches and it does.

The log grows to about 110GB before I run out of disk space and it´s forced to rollback.

There are no triggers on the table but 16 Non-clustered indexes, no clustered index. If I disable all indexes before running the operation it finishes without ever growing the log from the original size.

So my question is, am I logging every delete from every index when deleting from the table? If so, is that normal behaviour or could the reason be because of missing clustered index?

Improve this question
  • How long does your 18 million row delete run for? Is there anything else running on the server at the same time? – AMtwo Nov 27 '20 at 11:37
  • having non-clustered indexes on a heap is not a best practice. I don't know if that could explain your issue but would try to add a cluster index on your table and see if that changes the behavior. – Dominique Boucher Nov 27 '20 at 13:08
  • @AMtwo, It runs for about 1.5 hours reaching 110GB, with rollback. Also it takes about 10 minutes to finish with the indexes disabled. – Bergmeister Nov 27 '20 at 13:53
  • @DominiqueBoucher, You are right. This comes like this from the vendor. I did not know about it until now. I will test adding a clustered index. – Bergmeister Nov 27 '20 at 13:54
  • @AMtwo, no nothing else running on the server at the same time. – Bergmeister Nov 27 '20 at 15:25
5

So my question is, am I logging every delete from every index when deleting from the table?

Of course you are. The log contains a record for every change to every page with sufficient detail to either redo or undo the change.

If so, is that normal behaviour or could the reason be because of missing clustered index?

Unrelated. You're deleting 18 million rows from each of 17 seperate "indexes", 16 nonclustred indexes and one heap.

And it's safer to disable/rebuild the non-clustered indexes than drop/create them because the index definitions are retained on the server.

Improve this answer
2

Yes, index modifications are logged in pretty much the same way as "data" modifications.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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