February 7, 2009 at 11:23 am
With SQL Server 2005 I am deleting 8M rows, each row is 30 byte in size. Thus, the size of the deleted data is 240MB. However, the transction log grows by 15GB and my question is why there is so much overhead (98,4%)?
I have one FK in and one FK out from the table, if it matters.
Cheers //Jonas
February 7, 2009 at 11:58 am
DELETE statement is fully logged by SQL server, so when you are deleting 8M rows then each row operation is logged into tlog. If you do not want to be recorded in tlog you can change the recovery model temporarily and then change it back to FULL once the operation is done.
If you backup the tlog, the server should trauncte the log
February 7, 2009 at 12:02 pm
The question was: Why does the transaction log grow by 15GB when the deleted data is only 240MB.
Cheers//Jonas
February 7, 2009 at 12:13 pm
How are you deleting the rows? Are you deleting with a single DELETE statement or are you deleting the rows as batches. If you delete as a single statement then it will grow the transaction log because it is a single transaction.
If you do it in batch wise then the log space will control the size of the logs and will perform better
post your delete statement.
February 7, 2009 at 12:24 pm
I delete the rows in a single go: "DELETE T1 WHERE T2ID=NN"
So, you are saying that the transaction log will not increase its size as much if I delete the rows one by one. What is the reason for that?
Cheers//Jonas
February 7, 2009 at 12:37 pm
"DELETE T1 WHERE T2ID=NN"
Is this statement causing you to delete 8M rows???
According to msdn:
Log records in the transaction log files are truncated on a transaction-by-transaction basis. If the transaction scope is large, that transaction and any transactions started after it are not removed from the transaction log unless it completes. This can result in large log files. If the transaction is large enough, the log file might use up the available disk space
In your statement there is no commit statement after each row deletion and the log grows as it has to hold all deleted rows to be able to perform rollback if you have to. Deleting rows with subsequent commit statements or END statements allows transaction log to truncate the active portion of the log, thus managing the space efficiently.
February 7, 2009 at 12:48 pm
Yes.
My question is still: if I delete 240MB data, why does the transaction log grow by 15GB?
February 7, 2009 at 12:59 pm
Jonas (2/7/2009)
Yes.My question is still: if I delete 240MB data, why does the transaction log grow by 15GB?
It was already answered in this:
In your statement there is no commit statement after each row deletion and the log grows as it has to hold all deleted rows to be able to perform rollback if you have to. Deleting rows with subsequent commit statements or END statements allows transaction log to truncate the active portion of the log, thus managing the space efficiently.
the transaction log quickly grows as it needs to keep track of all the uncommitted rows that are being deleted
February 7, 2009 at 1:11 pm
In your statement there is no commit statement
I don't have any "begin transaction" statement.
...and the log grows as it has to hold all deleted rows
The size of all deleted rows is 240MB.
the transaction log quickly grows as it needs to keep track of all the uncommitted rows that are being deleted
The size of the uncommitted rows is also 240MB.
What you are saying doesn't seem to have bearing on why there is an apparent overhead in the transaction log of about 98%.
Cheers///Jonas
February 7, 2009 at 1:45 pm
There are also changes to any indexes that need to be logged in case of a rollback. In addition, when you are doing the delete, what else is going on in the system? Do the foreign keys have cascading deletes enabled, thereby deleting rows in child tables as well?
February 7, 2009 at 2:02 pm
Lynn Pettis (2/7/2009)
There are also changes to any indexes that need to be logged in case of a rollback.
Thanks. Yes, this might be the case. I will drop them temporarily to see what difference it makes.
In addition, when you are doing the delete, what else is going on in the system?
I am the only logged on user on the machine and nothing else is going on.
Do the foreign keys have cascading deletes enabled, thereby deleting rows in child tables as well?
No, I don't have cascading deletes or triggers.
I have started to suspect that 98% of the transaction log is filled with empty space, causing it to become very fragmented. Any known reason for this?
I will also try Krishnas suggestion to divide the delete into batches to see if this resolves any possible fragmentation of the transaction log.
Cheers//Jonas
February 7, 2009 at 2:32 pm
is the 15GB log growth the size of the physical log or actual space used, you should use dbcc sqlperf(logspace) to tell you how much actual logspace is used within the logfile. If you are just checking the physical size of the log it has bo bearing because that will depend on the log growth settings you have.
The log stores a lot more than just the data that was deleted, it will store the LSN, who deleted it, a timestamp and probably a whole lot more.
---------------------------------------------------------------------
February 7, 2009 at 3:03 pm
george sibbald (2/7/2009)
is the 15GB log growth the size of the physical log or actual space used, you should use dbcc sqlperf(logspace) to tell you how much actual logspace is used within the logfile. If you are just checking the physical size of the log it has bo bearing because that will depend on the log growth settings you have.
Good points. I should clarify that when the experiment started I had a physical logfile size of 2GB (unknown actual data size). Then I deleted 8 million rows, each with a size of 30 bytes. So the total amount of data that I deleted was 240MB. What I saw was that the physical logfile size grew to 17GB. The logfile was set to grow 10% as needed.
The log stores a lot more than just the data that was deleted, it will store the LSN, who deleted it, a timestamp and probably a whole lot more.
If the transaction log consists of data 30 bytes per row and overhead per row is ~2000 bytes everything makes sense.
Cheers//Jonas
February 7, 2009 at 4:35 pm
In your statement there is no commit statement
I don't have any "begin transaction" statement.
then "begin transaction" and "end transaction"/"commit transaction"
...and the log grows as it has to hold all deleted rows
The size of all deleted rows is 240MB.
check cascading deletes
the transaction log quickly grows as it needs to keep track of all the uncommitted rows that are being deleted
The size of the uncommitted rows is also 240MB.
same as above
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply