Why The Enormous Log File From a Delete?

rberman
rberman used Ask the Experts™
on
(SQL Server 2008)

I have a database about 12GB in size. It is composed of a number tables holding data organized into "scenarios" - basically a single small master table with several dozen detail tables. A scenario is typically about 2GB of data (that's the size the database increases by adding one).

I recently added relationships from the master table to the many detail tables with cascade on update/delete, because of the need to delete a scenario.

To test, I renamed one by changing the name in the master table. After maybe 40 minutes or so the transaction log file, which started at about 1MB, was 14GB in size and I aborted the operation. I am using simple logging.

I first thought perhaps this was because the PKs on these tables are clustered and the field being updated on these child tables is part of the key - so I thought probably the entirety of the tables was being re-created or something - and some of them are huge. So... redefined most as non-clustered, but the result is the same.

I don't understand how changing this value causes the log to expand to larger than the entire database (and with no end in sight), when the data itself is maybe 1/4th the database.


I don't want to get into the issue of why the tables or keys are designed this way because this is a database designed by others and which I have to work with - I can't at this time do major redesign of the database.

What's going on here? Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ved Prakash AgrawalSenior Manager, Database Engineering

Commented:
because your table are huge and you trying to make changes it. for that if will creat a temp table to store your data and drop table and then import back means if you make any change in the table it will drop and create using temp table.


 
Ved Prakash AgrawalSenior Manager, Database Engineering

Commented:
just check the recovery model of your database. if it is in full or bulk recovery mode change to simple.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What you want to mean with: " I am using simple logging."?

Log files have all changes you make in your database, so if you are working with millions of rows it's normal that the log file grows and even pass the data file size.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012
Commented:
Since you are already using Simple Recover Model, your only choice is to make changes it batches, this will allow transactions marked as inactive in the Transaction Log to be re-used.

Author

Commented:
WMontalvao: "Simple logging" - meant simple recovery model.  One of the types of logging you can specify for a database.

acperkins: Doesn't begin to address the question - how can my changing probably 1.5GB of data in a 12GB database result in a 14GB log file by the time I gave up? Even if every row of every table were in the log file it shouldn't be that large, but nothing like that should be logged.  I am not asking for a solution to what I should do INSTEAD of just deleting the related rows - of course I can break it down into chunks. I would like to understand why the log file grows to almost 10 times the size of the data being altered (assuming the full row of data even, rather than just the field being altered).

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok rberman. I just wanted to check if we was talking the same language :)

Even that can seems impossible it isn't. Your log file can really grows that much. All depends about want kind of operation are you doing and how often.

Author

Commented:
VMontalvao: I stated the operation: It is a delete operation - a single delete transaction. But here's more info - the delete covers about 1 to 2 GB of data spread out over probably 2 million rows, which are distributed in about 70 tables. The operation grows the log by AT LEAST 14GB - at which time I've canceled the operation. I don't know how large the log might eventually get if I let it run. I may try that on a larger system than my development system.
Top Expert 2012

Commented:
>>but nothing like that should be logged.  <<
Despite popular myth, even in Simple Recovery Model there is logging involved.

>>I don't know how large the log might eventually get if I let it run. <<
And you will never know if you keep interrupting it.

Good luck.

Author

Commented:
acperkins: I fully expect logging - I am saying that 14gb (and still growing) of logging to update at MOST 2GB of data - and in reality a lot less - is unreasonable.

As for interrupting it - maybe if my disk automatically grew more GB it would keep running. Afraid there was no choice in that configuration. I kinda thought 15GB of free space - which is all there is on that system- was enough.

It really doesn't matter how large it might get - the operation is unreasonable in time and disk space. If you have some idea as to what might be done about it, THAT would be most welcome.
Top Expert 2012

Commented:
>>If you have some idea as to what might be done about it, THAT would be most welcome<<
I tried.  Your response: "Doesn't begin to address the question"

Now I need to get on and help people that actually need my help.

Good luck again.

Author

Commented:
Sorry you feel that way. Not sure how putting down (incorrectly) my understanding of logging, or wondering how much further overlarge the log file would get was helping. Yes, please move on.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
rberman, you have delete triggers in those 70 tables?

Author

Commented:
VM: Good question. No - no triggers of any kind on any of them - this should be just a simple cascade delete.

Author

Commented:
Nope - acperkins did not answer the actual question, but some other question I never asked. I say it is simply abandoned, without answer.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
with due respect, but acperkins did both:

* explain that the log file IS used, even in simple recovery mode, and that you must let the statement complete
* unless you break the statement into batches to delete, for example, 1000 rows at once, which is the solution to have not such big .ldf file as the result of your delete ...

but I will leave the decision to the moderator that has been called by your objection.

a3

Author

Commented:
Afraid not - the question was why does deleting about 1.5GB of data result in logs many times larger than the deleted data. I did not ask whether logs were used, or why.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
the reason is that for the data that is deleted, the log has some overhead to be stored.
and as you deleted not from a single table, but from multiple (via the foreign key cascaded), the overhead is significant.
the "exact" calculations are not known to me, you would need to contact MS about the "formulas" ...

Author

Commented:
Leaving this to the moderator now

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial