I've been working in a SQLServer 2012 database for several years at work and I have always wondered about a strange thing I have observed with transaction logs. I 've been searching on Internet and StackOverflow for an explanation without success. I would like to understand what is happening before doing anything else.
The lasts days I have created a new database with default options and again I observe the same situation. The database has been defined with Simple recovery model.
The situation is simple to explain. I have a simple table with a Primary key only. This table has been created with default options (no replication, no partition, no relationship with any other tables etc..). It's just a simple and isolated table.
This table has about 380.000 rows and has a data size in disk of 70 Mb and an index size of 23 Mb aproximately. When I do a DELETE FROM schema.table of this table, the transaction log size grows until 500 Mb. The transaction log has a size limit of 500 Mb, so the transaction finally fails. I am the only person working on this database. The initial size of the transaction log is 10 Mb and has a growth size of 10 Mb.
The question is: why SQLServer needs 500 Mb of log size to delete about 93 Mb of data (data + index)? I can't understand the reason. Of course I can make the transaction log size limit bigger. But this is my last option and before I want to understand what's happening. Also I don't want to do a TRUNCATE TABLE because I will need to do filtered deletions in the future with that number of rows.
Edit: finally I haver tried to increase the transaction log file limit size to 1 Gb. Executing the DELETE command it needed finally 530 Mb of size. 530 Mb to delete 93 Mb of data ? Is that normal ?
Thanks for any answer that can give me a light on it.