Extremely bloated transaction log during delete 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?
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 &quot;indexes&quot;, 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.