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?